CODEWITHSUNDEEP
oracle-database
Kamilski81
Kamilski81

Reputation: 15127

How do I copy or import Oracle schemas between two different databases on different servers?

What is the best way to copy schema from one user/instance/server:

jdbc:oracle:thin:@deeb02:1535:DH, user pov

to another user/instance/server

jdbc:oracle:thin:@123.456.789.123:1523:orcl, user vrs_development

?

Upvotes: 6

Views: 37921

Answers (3)

ildar
ildar

Reputation: 109

you can directly copy schema via network (without moving files from one server to another) using datapump parameter NETWORK LINK as described here:

http://vishwanath-dbahelp.blogspot.com/2011/09/network-link-in-datapump.html

for example:

impdp -userid user/pass@destination_server LOGFILE=log.txt NETWORK_LINK=dblink_from_dest_to_source SCHEMAS=schema1 directory=DATA_PUMP_DIR

check that directory DATA_PUMP_DIR exists in 

select *
from dba_directories

and points to correct place in destination_server file system.

Upvotes: 0

Aaron
Aaron

Reputation: 57808

Similarly, if you're using Oracle 10g+, you should be able to make this work with Data Pump:

expdp user1/pass1@db1 directory=dp_out schemas=user1 dumpfile=user1.dmp logfile=user1.log

And to import:

impdp user2/pass2@db2 directory=dp_out remap_schema=user1:user2 dumpfile=user1.dmp logfile=user2.log

Upvotes: 8

Raihan
Raihan

Reputation: 10425

Use oracle exp utility to take a dump of the schema from the first database 

exp user1/pass1@db1 owner=user1 file=user1.dmp log=user1.log

Then use imp utility to populate the other schema in the other datbase

imp user2/pass2@db2 fromuser=user1 touser=user2 file=user1.dmp log=user2.log

Upvotes: 5

Related Questions