oracle資料泵不同工作方式效能比較（二）

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

首先檢查來源資料庫的資料表空間是否滿足自包含條件：

SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TJSQ,TJSQ_TMP')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

設定來源資料庫遷移資料表空間為唯讀狀態：

SQL> ALTER TABLESPACE TJSQ READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE TJSQ_TMP READ ONLY;

Tablespace altered.

下面利用資料泵執行資料表空間遷移的匯出操作：

SQL> HOST

[oracle@yans2 ~]$ expdp system directory=d_dmpdp dumpfile=tjsq_090617_trans_tablespace.dp transport_tablespaces=tjsq, tjsq_tmp

Export: Release10.2.0.3.0 - 64bit Production on Wednesday, 17 June, 2009 18:07:29

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=d_dmpdp dumpfile=tjsq_090617_trans_tablespace.dp transport_tablespaces=tjsq, tjsq_tmp

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_TABLE_ACTION

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW_LOG

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/data/dmp/tjsq_090617_trans_tablespace.dp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 18:08:30

可以看到由於不需要匯出表中的資料，因此匯出操作十分迅速就完成了，只用了1分1秒。

下面利用DBMS_FILE_TRANSFER包進行傳送：

SQL> SET TIMING ON

SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq01.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq01.dbf')

PL/SQL procedure successfully completed.

Elapsed: 00:30:57.84

SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq02.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq02.dbf')

PL/SQL procedure successfully completed.

Elapsed: 00:06:29.41

SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq_tmp.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq_tmp.dbf')

PL/SQL procedure successfully completed.

Elapsed: 00:06:28.63

SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DMPDP', 'tjsq_090617_trans_tablespace.dp', 'NEWDEMO', 'D_DMP', 'tjsq_trans_tablespace.dp')

PL/SQL procedure successfully completed.