oracle資料泵不同工作方式效能比較（三）測試直接路徑匯出、匯入方式

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

這篇測試直接路徑匯出、匯入方式。

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

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資料泵的預設匯出、匯入都是直接路徑方式，因此當前的測試不需要進行額外的設定。

下面利用資料泵的匯出操作：

[oracle@yans2 dmp]$ expdp system directory=d_dmpdp dumpfile=tjsq_090617.dp schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper

查看本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/