How to Use Datapump Over a Database Link
On the local database create link
CREATE PUBLIC DATABASE LINK MyRemoteDBLink
CONNECT TO MyRemoteDBUser IDENTIFIED BY MyRemoteDBUserPassword
USING 'MtRemoteDBTNSEntry'
Notes
- MyRemoteDBLink = Any name you like
- MyRemoteDBUser=A user on the remote database that has permission to export
- MyRemorteDBTNSEntry = A TNS Entry pointing to the remote database
Run the following command on the local host to Export the remote database to the local host
expdp system directory=DATA_PUMP_DIR LOGFILE=MyExpdp.log network_link=MyRemoteDBLink schemas=MyRemoteDBSchema1,MyRemoteDBSchema2
Run the following command on the local host to Import the dataump export file into the local database
impdp system directory=DATA_PUMP_DIR LOGFILE=MyImpdp.log schemas=MyRemoteDBSchema1,MyRemoteDBSchema2 exclude=STATISTICS
- Alternatively, if you do not want to stage the export file on the local host you can run the following command from the local host
impdp system directory=DATA_PUMP_DIR LOGFILE=MyExpdp.log network_link=MyRemoteDBLink schemas=MyRemoteDBSchema1,MyRemoteDBSchema2
