Datapump and network links

I was asked to develop a solution which copied data from one schema to another. The solution was required to be generic so would work if the schemas resided on different databases.

I thought this was a good situation to utilise at Data Pump rather than a one off set of scripts.

When I reviewed the Oracle documentation the following was needed:
- A database link between source and target databases
- Specify NETWORK_LINK in the parameter file

Creation of a database link can be done as follows:

 CREATE DATABASE LINK loopback  
 CONNECT TO scott  
 IDENTIFIED BY "xxx"   
 USING 'orcl11g';  

As can be seen in the code above, the link is called "loopback". It creates to the user SCOTT using the password XXX and connects to the orcl11g database.

Next step is to confirm the link is working:

 SELECT COUNT(*)  
 FROM  emp@loopback;  
 COUNT(*)  
 ----------  
    14  

Works fine. Now for the datapump parameter file.

 CONTENT=ALL  
 FULL=N  
 LOGFILE=import.log  
 METRICS=YES  
 NETWORK_LINK=loopback  
 REMAP_SCHEMA=scott:top  
 TABLES=scott.emp  
 TABLE_EXISTS_ACTION=TRUNCATE  

The important parameters are:
- CONTENT, when ALL then metadata and data is transferred
- NETWORK_LINK, set to use the LOOPBACK database link created above
- REMAP_SCHEMA, specifies the source and target schemas
- TABLES, only copy the EMP table between the schemas. Note that I have prefixed the EMP table with the source schema name - this prevents the errors ORA-39166: Object TOP.EMP was not found.
ORA-31655: no data or metadata objects selected for job

Now for the datapump import. I used the following command

 impdp top parfile=scott_imp.par  

Finally, check that the table is populated in the target schema

 SELECT COUNT(*)  
 FROM top.emp;  
 COUNT(*)  
 ----------  
    14  

As an aside, I received the following error when testing:

 ORA-31631: privileges are required  
 ORA-39149: cannot link privileged user to non-privileged user  

To resolve, I granted the roles IMP_FULL_DATABASE and EXP_FULL_DATABASE for both the source and target users.