How to Import data without Dumpfile-Network Mode Import

It is possible to import data in a database without the dump file. From network the data will be retrieved from one database and then import that data back to the target database. There are no dump files involved.

How to do the Network Mode Import:
-------------------------------------
1)Create a database link (with the source database) in the database where you will perform the import operation.

SQL> create database link ARJU.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to arju identified by a using 'NEPTUNE';
Database link created.

In your tnsnames.ora you have entry with NEPTUNE and it works correctly.

I have to used this long name because the database global name settings.

2)Check the database link works:
If destination table name is select perfectly then your database link is ok.
SQL> select table_name from user_tables@ARJU.REGRESS.RDBMS.DEV.US.ORACLE.COM;
TABLE_NAME
------------------------------
FIRST_TABLE
TEST
NAME
In source database you can check by,
SQL> select table_name from user_tables;
no rows selected

3)Connect to the source database and note down the SCN of the database. This SCN is needed in order to ensure consistency in the dumpfile. You can do it while you are sitting on destination database by helping database links.
SQL>SELECT CURRENT_SCN FROM V$DATABASE@ARJU.REGRESS.RDBMS.DEV.US.ORACLE.COM;
118740640

Also you can logon to that database and see SCN.

4)Perform the network mode import operation.Specify database link parameter with NETWORK_LINK. Also specify FLASHBACK_SCN that you find in step 3.

SQL> host impdp arju/arju directory=dnet FLASHBACK_SCN=118740640 NETWORK_LINK=ARJU.REGRESS.RDBMS.DEV.US.ORACLE.COM

Note that no dumpfile parameter here. I created dnet directory into which log file will be written.

Restrictions of Network Mode Import:
-------------------------------------------

1.If the source database is read-only, then the user on the source database must have a locally-managed tablespace assigned as a default temporary tablespace. Otherwise, the job will fail.

2.If the USERID that is executing the import job has the IMP_FULL_DATABASE role on the target database, then that user must also have the EXP_FULL_DATABASE role on the source database.

3.The only types of database links supported by Data Pump Import are: public, fixed-user, and connected-user.

4.When the NETWORK_LINK parameter is used in conjunction with the TABLES parameter, only whole tables can be imported (not partitions of tables).

5.Network imports do not support the use of evolved types.

Related Documents:
Extract DDL from Dump