Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    30

    Unanswered: 2 database on One Network

    I have Installed 2 Database on different Machines.
    Now i want to IMPORT data Direct from One Database to 2nd Database.
    How can I Import Data Into MY Primary Domain Database.

    Please Help me.
    I shall be very thank full to all of U.
    Kashif

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    That depends on which version of Oracle you are using. However, all versions have one requirement in common: to copy data "directly" from one DB to another requires you setup a DB Link between the two DBs. You do not need the same user account in both databases, but it make this a little easier.

    In Oracle 10g, use the Data Pump Import to import data and metadata directly from one Oracle database into another.

    If you are using 9i or lower, then you can do a CTAS operation over the DB Link. For example, in your target database you issue the command CREATE TABLE <TABLENAME> AS SELECT * FROM <TABLENAME>@DBLINKNAME; I would create a procedure with a cursor that loops through your list of tables automatically. Below I will provide a sample in draft - since I am typing this now I have not tested it.

    declare
    cursor c1 is select table_name from user_tables@marktest.remidata.com;
    v_tname varchar2(30);
    begin
    open c1;
    loop
    fetch c1 into v_tname;
    exit when c1%notfound;
    v_sql := 'create table ' || v_tname || ' as select * from ' || v_tname
    || '@markold.remidata.com';
    execute immediate v_sql;
    end loop;
    close c1;
    exception
    when others then dbms_output.put_line ('this procedure needs more work.');
    end;
    /
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Or, having a DB link you can just COPY the tables:
    Code:
    COPY FROM uid1/pw1@db01 TO uid2/pw2@db02 
    CREATE MyTable  -- or APPEND or INSERT or REPLACE
    USING Select ... From ...;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •