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.