使用impdp network link 跳過expdp直接匯入資料

目標資料庫: rman02

實驗步驟：

1. 配置源資料庫與目標資料庫的tnsnames.ora資訊

2. 在源資料庫建立測試表，建立public db link

3. 在目標庫使用 imp network_link 引數  直接匯入schema

4. 檢視測試結果

------------------------------------------------------------------------------------------------------

1. 配置源資料庫與目標資料庫的tnsnames.ora資訊

源資料庫tnsname.ora配置資訊如下:

[oracle@localhost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RMAN02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.248.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rman02)
    ) 
)
RMAN01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rman01)
    )
  )

目標資料庫 tnsnames.ora配置資訊如下：

[oracle@localhost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RMAN01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.248.132)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rman01)
    )
  )

RMAN02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rman02)
    )
)

2. 在源資料庫建立測試表，建立public db link

SQL> conn user01/gaoxu
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

SQL> create public database link rman02_to_rman01 connect to system identified by gaoxu using 'RMAN01';

Database link created

3. 在目標庫使用 imp network_link 引數  直接匯入schema

[oracle@localhost dpdump]$ echo "/as sysdba" | impdp directory=EXP_PUMP_DIR logfile=rman02_to_rman01.log network_link=rman02_to_rman01 schemas=user01 remap_schema=user01:user02 

Import: Release 11.2.0.1.0 - Production on Wed Jul 30 20:50:05 2014

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

Username: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA directory=EXP_PUMP_DIR logfile=rman02_to_rman01.log network_link=rman02_to_rman01 schemas=user01 remap_schema=user01:user02 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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

. . imported "USER02"."TEST"                                 37 rows

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 20:52:23

4. 檢視測試結果

SQL> conn user02/gaoxu
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

SQL> select count(*) from test;

  COUNT(*)
----------
        37