copy strucure and data from oracle

Question

User-914991384 posted

Hi,

How to copy structure and data of table from one database to another database.

ex: i have prod db contains of 100's of tables pointing to service name and hosted in a server.

I have a test db where i want to copy some tables of prod. this test db is pointing to different service name and hosted in different server.

which is the simplest and quickest way to do that

Answer 1

User269602965 posted

If you are using Oracle 10g or 11g,

then DATAPUMP is the best way to move tables between database instances,

and if needed, remap data table to a new SCHEMA name.

Answer 2

User-914991384 posted

Lannie,

can you show some examples how to use datapump.

thanks

Answer 3

User269602965 posted

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_datpmp.htm#i1007277

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_dp.htm

http://www.morganslibrary.com/reference/datapump.html

 

Answer 4

User651454269 posted

you can use Oracle export and import commands. I haven't done in latest versions but I have done in 9i. It has exp and imp command. But you have to create the user(schema) related to taget database if it doesnt exist.

other solution is create link beween db and create the table using create table as method in sql.