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’;
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.