Copy / refresh schema using Datapump over network (db link)
When you try to move large tables or schema between two Oracle databases, datapump export might take lot of disk space. The exported dump files may take lot of space on the disk. The best workaround is to use dblink with Oracle datapump to move data from one oracle database to another.
Note: when you use datapump with dblink, there are no dumpfiles created on disk. The data is transferred from one database to another over network.
Prepare Target Database
Even before you can jump and start copying data from one database to another, we need to prepare the target database to accept the incoming data from another database.
This step is optional, you can use sys user on target database to import schema over dblink.
SQL> create user new_scott identified by tiger; User created. SQL> grant connect, resource to new_scott; Grant succeeded. SQL> create or replace directory MY_DUMP_DIR as '/u01/dump_files'; Directory created. SQL> grant read, write on directory MY_DUMP_DIR to new_scott; Grant succeeded. SQL> grant create database link to new_scott; Grant succeeded.
Add source TNS entry
At this point, we need to make our target database able to speak to source database. Add source database TNS entry into tnsnames.ora of the target database.
devdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = devdb) ) )
Create DB Link
On target, we need to create a database link using the TNS entry created above.
connect new_scott/tiger Connected. create database link SOURCE_DB connect to scott identified by tiger using 'devdb'; Database link created.
Import Single Schema via DB Link
Now that you have prepared the target database, its time to import source schema on target database via db link. Run below command on target database to import source schema via dblink over network.
impdp new_scott/tiger directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log network_link=SOURCE_DB remap_schema=scott:new_scott
Directory = place where logfiles will be placed
Logfile = Name of the logfile placed in above directory
Network_Link = The db link to be used (created above)
Remap_Schema = Import source scott schema into target new_scott schema
Import Multiple Schema via DB Link
To import multiple schemas, make sure to use a DBA user (sys) to perform this action.
impdp sys directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log network_link=SOURCE_DB schemas=IJS,scott,hr