Network linki kullanarak EXPDP ve IMPDP işlemlerinin yapılması

Export ve import pompalama işlemlerinde kaynak veriyi hedef veritabanı sunucusuna taşımak için network bağlantısı methodunu kullanmak faydalı olabilmektedir.impdp işleminde veritabanından başka bir veritabanına herhangi bir aracı dosya kullanmadan direk taşıma yapılabilmektedir. Bu işlemin adım adım uygulandığı bir senaryo aşağıda yer almaktadır.

1. Kaynak veritabanında tnsnames.ora dosyası içinde hedef veritabanı için bir TNS girişi oluşturuyoruz ve bu TNS girişini kullanarakta bir veritabanı linki oluşturuyoruz.

TARGET_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = testdb2)
) )

SQL> create database link target_db connect to hr identified by hr using 'TARGET_DB';

2. Bu veritabanı linkini impdp veya expdp komutunda belirtiyoruz.

IMPDP işlemi alttaki gibi olabilmektedir:

impdp hr/hr directory=dumpdir logfile=imp.log network_link=target_db schemas=hr

Import: Release 11.2.0.1.0 - Production on Sat Jan 29 12:06:44 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

Starting "HR"."SYS_IMPORT_SCHEMA_01":  hr/******** directory=dumpdir logfile=imp.log network_link=target_db schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "HR"."DEPARTMENTS"                                   14 rows
. . imported "HR"."EMPLOYEES"                                   107 rows
. . imported "HR"."COUNTRIES"                               5 rows
. . imported "HR"."JOBS"                                  14 rows

. . imported "HR"."JOB_HISTORY"                                  14 rows

. . imported "HR"."LOCATIONS"                                  25 rows

. . imported "HR"."REGIONS"                                  18 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 12:14:19

IMPDP işlemi ise alttaki gibi olabilmektedir:

expdp hr/hr directory=dumpdir logfile=imp.log network_link=target_db schemas=hr dumpfile=newhr.dmp

Export: Release 11.2.0.1.0 - Production on Sat Jan 29 12:16:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/******** directory=dumpdir logfile=imp.log network_link=target_db schemas=hr dumpfile=newhr.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . imported "HR"."DEPARTMENTS"                                   14 rows
. . imported "HR"."EMPLOYEES"                                   107 rows
. . imported "HR"."COUNTRIES"                               5 rows
. . imported "HR"."JOBS"                                  14 rows

. . imported "HR"."JOB_HISTORY"                                  14 rows

. . imported "HR"."LOCATIONS"                                  25 rows

. . imported "HR"."REGIONS"                                  18 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/newhr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:20:04