oracle資料泵不同工作方式效能比較（五）測試NETWORK_LINK匯入方式

根據Oracle的文檔的描述，資料泵採用不同的方式匯出匯入，效能也會有明顯的差別，這次正好有機會測試一下，遷移資料表空間、直接路徑、外部表格方式，以及資料庫鏈方式匯出、匯入的效能差異。

這篇測試NETWORK_LINK匯入方式。

首先清除上一篇文章中匯入的使用者和資料表空間，並重建立立測試使用者和資料表空間。

SQL> DROP USER TJSQ_NDMAIN CASCADE;

User dropped.

SQL> DROP USER TJSQ_TRADE CASCADE;

User dropped.

SQL> DROP USER TJSQ_GOV CASCADE;

User dropped.

SQL> DROP USER TJSQ_NDMAIN_OPER CASCADE;

User dropped.

SQL> DROP USER TJSQ_TRADE_OPER CASCADE;

User dropped.

SQL> DROP TABLESPACE TJSQ INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE TJSQ_TMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

下面重新建立使用者，並設定許可權：

SQL> CREATE USER TJSQ_NDMAIN IDENTIFIED BY TJSQ_NDMAIN;

User created.

SQL> CREATE USER TJSQ_TRADE IDENTIFIED BY TJSQ_TRADE;

User created.

SQL> CREATE USER TJSQ_GOV IDENTIFIED BY TJSQ_GOV;

User created.

SQL> CREATE USER TJSQ_NDMAIN_OPER IDENTIFIED BY TJSQ_NDMAIN_OPER;

User created.

SQL> CREATE USER TJSQ_TRADE_OPER IDENTIFIED BY TJSQ_TRADE_OPER;

User created.

SQL> GRANT CONNECT TO TJSQ_GOV;

Grant succeeded.

SQL> GRANT RESOURCE TO TJSQ_GOV;

Grant succeeded.

SQL> GRANT CONNECT TO TJSQ_NDMAIN;

Grant succeeded.

SQL> GRANT RESOURCE TO TJSQ_NDMAIN;

Grant succeeded.

SQL> GRANT CONNECT TO TJSQ_NDMAIN_OPER;

Grant succeeded.

SQL> GRANT RESOURCE TO TJSQ_NDMAIN_OPER;

Grant succeeded.

SQL> GRANT CONNECT TO TJSQ_TRADE;

Grant succeeded.

SQL> GRANT RESOURCE TO TJSQ_TRADE;

Grant succeeded.

SQL> GRANT CONNECT TO TJSQ_TRADE_OPER;

Grant succeeded.

SQL> GRANT RESOURCE TO TJSQ_TRADE_OPER;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO TJSQ_GOV;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO TJSQ_NDMAIN;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO TJSQ_NDMAIN_OPER;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO TJSQ_TRADE;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO TJSQ_TRADE;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO TJSQ_TRADE_OPER;

Grant succeeded.

資料表空間的建立以及資料檔案初始化的時間需要單獨計時：

SQL> SET TIMING ON

SQL> CREATE TABLESPACE TJSQ DATAFILE '/data/oracle/oradata/tjsq/tjsq01.dbf' size20g,

 2  '/data/oracle/oradata/tjsq/tjsq02.dbf' size4g;

Tablespace created.

Elapsed: 00:02:07.68

SQL> CREATE TABLESPACE TJSQ_TMP DATAFILE '/data/oracle/oradata/tjsq/tjsq_tmp.dbf' size4g;

Tablespace created.

Elapsed: 00:00:22.19

這個步驟總用時2分30秒。

由於Oracle資料泵的預設匯出、匯入都是直接路徑方式，因此當前的測試不需要進行額外的設定。

使用NETWORK_LINK方式執行匯入是否的簡單，不需要匯出和傳送的過程，只需要一個impdp命令就可以完成所有的操作：