Exporting table from one database to another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Exporting table from one database to another

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    Exporting table from one database to another

    I need to export a table with data from a Production database to a development database. There is a db link between them.

    prod ---> dev
    Tablename= table1
    db link on prod pointing to dev = dev.us.oracle.com

    How do I do this?

    Thanks.

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Well, it depends. If the table is not too big you can use SQL "copy" command through the db_link.

    Or you can use export/import but that will require you FTP'ing the .DMP file over to developement.
    How big is the table?
    Does it exist in developement?
    What is the square root of 945,890,321.6?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Oct 2003
    Posts
    38
    Go to development machine. Point the dblink to production.
    Set your environments pointing to the development databsae you want to import the table.
    FTP the export file from the production server.
    Diable all triggers,primary keys, fKeys on the development machine.
    truncate the table.
    IMprot into table using grant=N and FULL=N.
    Enable the PK,FK,Triggers.===
    IF you have sequence in your table, Generate a crete sequence script on you proudction database with the last_number from user_sequences.
    If you are refreshing all table you need below script, if you are refreshing one table mention the sequence name in the below script.
    Your script should be like SPOOL /temp/create_seq.sql.
    select 'DROP SEQUENCE '||sequence_name||';' from user_sequences;
    select 'CREATE SEQUENCE '||sequence_name||
    '
    INCREMENT BY 1 START WITH '||last_number||
    ' MINVALUE 1
    MAXVALUE 9999999999999999999999999999
    NOCYCLE ORDER
    NOCACHE ;'
    from user_sequences@prod.us.oracle.com;

    select 'GRANT SELECT ON '||sequence_name||' TO USERNAME;' from user_sequences;
    SPOOL OFF
    Make sure to give username in place of USERNAME.

    And run the script on development machine, Your table is refershed as your production.

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Wooahhh woaahh what is all that mess Prince??

    Mr.Pcotton is just mearly wanting to transport a table from one database to another. He didn't say anything about the table already being on the dev server,unless of course he forgot to mention it (which happens a lot in this place). Like Hanky said if the table isn't too big a simple export and import will do the trick.

    Ahhh I love it when you have database links from Prod to Dev!!
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Jan 2001
    Posts
    138
    Thanks for the input. I did export --> ftp ---> import. Tables already existed on dev.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Mr.Hanky
    Well, it depends. If the table is not too big you can use SQL "copy" command through the db_link.
    MH
    er, you dont need db link to use copy

Posting Permissions

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



Click Here to Expand Forum to Full Width