One of the nicest features that came out in Oracle 10g was datapump. One of the least publicized but nicest capabilities of datapump is the ability to transfer data directly from one instance to another without using a datafile to transport the data. The data is transported across a db link from one instance to another. Datapump still requires a directory on the server to store some information, but the data itself is transferred directly. This is a huge time and space saver for those large data transfer operations. This is how it is done:
SQL> create directory dpump_dir as ‘/tmp’;
SQL> create public database link source_db using ‘source_db’;
SQL> exit
impdp system/system directory=dpump_dir network_link=source_db schemas=scott remap_schema=scott:dest_user;
The command above will import the scott schema from source_db to the current database, and rename the schema to dest_user. If the dest_user does not exist, it will be created.
Andrew Kerber's Oracle DBA Weblog 
August 2, 2010 at 20:03 |
Where should I create the dpump_dir? I assume in the source server. I tried that. But I still get error that the directory is invalid.
August 2, 2010 at 20:09 |
dpump_dir is directory created on the source server, but it must also be created within oracle with the oracle ‘create directory’ command, for example:
create directory dpump_dir as ‘/home/oracle’
make sure that the Oracle user has write permission on the directory.
August 3, 2010 at 17:33 |
Thanks. This is what I did. The key point is the data pump directory should be created in the target database and the path should be poiting to source server’s directory location.
1. Login as system and created a user called ORA_SUNDAR in the target server
2. Login as system to target server and recreate the directory data_pump_dir pointing to source server’s data_pump_dir location. Make sure to “Map Network Drive” to \\xxx\xxxx. Grant read, write permission on directory data_pump_dir to ORA_SUNDAR.
SQL> drop directory data_pump_dir;
Directory dropped.
SQL> create directory data_pump_dir as ‘ \\xxx\xxxx\’;
Directory created.
SQL> grant write,read on directory data_pump_dir to ORA_SUNDAR;
Grant succeeded.
3. Create a public linked server called “linka” and connect to source schema.
CREATE PUBLIC DATABASE LINK “LINKA” CONNECT TO ora_sundar IDENTIFIED BY ora_sundar USING ‘net service name’;
4. Open a DOS window in the target server and change the directory to the oracle bin directory.
C:\Documents and Settings\Oracle>cd C:\oracle\product\11.1.0\db_1\BIN
5. Run the data pump import. Example shows moving only one table called “client” from source to target. If you remove the tables parameter, the whole schema will be refreshed with objects from source schema.
impdp system/pwd tables=client network_link=LINKA directory=data_pump_dir remap_schema=ora_sundar:ora_sundar
August 3, 2010 at 17:43 |
Not quite, and I should have made this more clear. The directory must be defined on the database in which you are running datapump, the source or destination is irrelevant, it just mus exist in the database you are running in.
August 3, 2010 at 18:36 |
You are right. I created the directory pointing to “C:\” and I was able to do data pump import. Thanks
November 3, 2012 at 19:31 |
I blog often and I really appreciate your content.
This article has really peaked my interest. I will take a note of your site and keep checking for new details
about once per week. I opted in for your Feed too.
Forrest
February 9, 2015 at 19:53 |
Radical attempt bro. Like your effort.