Monday, August 10, 2009

how to import datapump [via database link] to Target DB

With network mode imports, one doesn't need any intermediate dump files (GREAT, no more FTP'ing of dump files). Data is exported across a [[database link]] and imported directly into the target database. Example:


oracle> sqlplus /as sysdba


SQL> create user test identified by test;

User created


SQL> grant connect, resource to test;

Grant succeeded.


SQL> grant read, write on directory dmpdir to test;

Grant succeeded


SQL> grant create database link to test;

Grant succeeded


SQL> conn test/test

Connected


SQL> create database link testnew connect to test identified by test using 'orcl.oracle.com'(SID.domain.com);

Database link created


oracle>impdp test/test DIRECTORY=dmpdir NETWORK_LINK=testnew remap_schema=a111(source database schema name):test remap_tablespace=a222(source database tablespace name):a3333(target database tablespace name)


All work is performed on the target system. The only reference to the source systems is via the database link.