Download
Using Network_Link in Datapump
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
