Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Copy Table Structure From One DB to Another

    I have 3 databases on my server and there is a table in DB1 that I'd like to create a replica of in DB2. Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    You can use copy command in SQL*Plus,

    I prefer creating a database link in DB2 connecting to DB1:


    [REMI@DB2.WORLD] CREATE DATABASE LINK DB1.WORLD CONNECT TO REMI IDENTIFIED BY REMIVISSER USING 'DB1.WORLD';

    Test the db link:

    [REMI@DB2.WORLD] SELECT * FROM global_name@DB1.WORLD;

    Now you can create the table in DB2 as a copy of the table in DB1

    [REMI@DB2.WORLD] CREATE TABLE copy AS SELECT * FROM original_table@DB1.WORLD;

    If you want to create an empty table issue (structure only):

    [REMI@DB2.WORLD] CREATE TABLE copy AS SELECT * FROM original_table@DB1.WORLD WHERE 1 = 2;


    Hope this helps!

    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I'm new to this type of thing so can you walk me through it? What do I use? SQL Worksheet? I bet your idea would work (For the structure only) but I don't know where to plug in your idea. Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    I think you can use SQL worksheet.

    I use SQL*Plus which should be located in your $ORACLE_HOME/bin directory. Where ORACLE_HOME is the directory where you installed the Oracle software tree. You should find there the sqlplus executale (on windows platforms there is the sqlplusW executable).

    Hope this helps!

    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Here is what I get:

    Code:
    SQL> CREATE TABLE IM AS SELECT * FROM PK1.IM@PK1 WHERE 1=2;
    CREATE TABLE IM AS SELECT * FROM PK1.IM@PK1 WHERE 1=2
                                            *
    ERROR at line 1:
    ORA-02019: connection description for remote database not found
    PK1 is the database and schema that the IM table is that I want to copy. Can you help? Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Did you create the databaselink.

    Test the database link:

    select * from global_name@<name_of_db_link>

    Test if you can see the table with the following syntax:

    select * from <table_owner>.<table_name>@<name_of_database_lin k>

    Please inform me if it worded!

    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Maybe this is out of my league but I don't even understand your code earlier. Could you tell me what is needed to create the link? I have:

    DB1 = Database with table in it
    PK1 = DB1 Schema

    DB2 = Database where copy will go
    PKDEMO = DB2 Schema

    Thanks, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I just created my database link. WOOHOO! I'll begin testing it and hopefully getting it to work. Thanks, Jeremy
    Nothing better than a good ride.

  9. #9
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Allrighty the:

    1. Login to DB2

    2. Create a database link which connects to DB1

    [REMI@DB2.WORLD] CREATE DATABASE LINK DB1.WORLD CONNECT TO <username_in_db1> IDENTIFIED BY <password_of_user> USING 'DB1.WORLD';

    3. Test if the database link works:

    Still connected in DB2 issue:

    [REMI@DB2.WORLD] SELECT * FROM GLOBAL_NAME@DB1.WORLD;

    This should return DB1[.WORLD]

    4. Still connected to DB2 create the copy:

    You listed:

    DB1 = Database with table in it
    PK1 = DB1 Schema

    DB2 = Database where copy will go
    PKDEMO = DB2 Schema

    I miss the table_name in DB1

    Your statemenent (IN DB2) would be:

    CREATE TABLE copy AS SELECT * FROM PK1.<fill_in_table_name>@DB1.WORLD;
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Database link didn't work. Doing more research. Thanks, Jeremy
    Nothing better than a good ride.

  11. #11
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    What is the ".WORLD" for? That is confusing the hell out of me.
    Nothing better than a good ride.

  12. #12
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Per Oracle Black Book:

    Code:
    create [public] database link REMOTE_CONNECT
    connect to [current_user | username identified by password]
    using 'connect string';
    Here is what I did:

    Code:
    SQL> create public database link Jeremy_Link
      2  connect to PK1 identified by password
      3  using 'system/orcl';
    
    Database link created.
    What does what I did do because it doesn't seem to work. Thanks, Jeremy
    Nothing better than a good ride.

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I think I did it right this time as I got a different error message:

    Code:
    Authentication adapter initialization failed
    Anyone know why this happens? Thanks, Jeremy
    Nothing better than a good ride.

  14. #14
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    CONNECT SYSTEM/MANAGER@<database>;


    GRANT CREATE DATABASE LINK TO <schema>;


    CONNECT <schema>/<password>@<database>;


    CREATE DATABASE LINK <database to link> CONNECT TO <schema to link> IDENTIFIED BY <password> USING 'xxxx';


    These databases are in the same world.
    Cordialement

  15. #15
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    Originally posted by JCScoobyRS
    What is the ".WORLD" for? That is confusing the hell out of me.
    It is the domain. (can be anything you like)

    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

Posting Permissions

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