-
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.
-
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.
-
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.
-
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
-
Thanks for the input. I did export --> ftp ---> import. Tables already existed on dev.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|