1 同时导出表和对应的索引(记下所用的时间)
2 同时导入表和对应的索引(记下所用的时间)
3 单独导出表(记录时间)
4 单独导入表(记录时间)
5 重建索引(记录时间)
实验表和索引的信息如下：
USER01@aaron> select count(*) from test01;
COUNT(*)
----------
4643904
USER01@aaron> col segment_name for a20
select segment_name,
segment_type,
tablespace_name,
blocks,extents,
bytes/1024/1024
from user_segments where segment_name = upper(‘&segment_name‘);USER01@aaron> 2 3 4 5 6
Enter value for segment_name: TEST01
old 6: from user_segments where segment_name = upper(‘&segment_name‘)
new 6: from user_segments where segment_name = upper(‘TEST01‘)
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024
-------------------- ------------------ ------------------------------ ---------- ---------- ---------------
TEST01 TABLE USERS 89856 159 702
开始实验：
1 同时导出表和对应的索引(记下所用的时间)
实验结果如下：
[oracle@localhost exp_dir]$ echo "/as sysdba" | expdp directory=EXP_DIR logfile=test01.expdp.log dumpfile=test01.expdp tables=user01.TEST01;
Export: Release 11.2.0.1.0 - Production on Fri Nov 14 00:57:12 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_EXPORT_TABLE_01": /******** AS SYSDBA directory=EXP_DIR logfile=test01.expdp.log dumpfile=test01.expdp tables=user01.TEST01
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 702 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "USER01"."TEST01" 448.4 MB 4643904 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/data/oracle/oradata/exp_dir/test01.expdp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:57:44
所用时间：00:57:44 - 00:57:12=32s
2 同时导入表和对应的索引(记下所用的时间)
[oracle@localhost exp_dir]$ echo "system/gaoxu" | impdp directory=EXP_DIR dumpfile=test01.expdp logfile=test.impdp.log;
Import: Release 11.2.0.1.0 - Production on Sun Nov 16 17:33:48 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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=EXP_DIR dumpfile=test01.expdp logfile=test.impdp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER01"."TEST01" 448.4 MB 4643904 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:35:31
所用时间：17:35:31-17:33:48=103s
总共用时：32s+103s=135s
3 单独导出表(记录时间)
[oracle@localhost exp_dir]$ echo "/as sysdba" | expdp directory=EXP_DIR logfile=test01.table.expdp.log dumpfile=test01.table.expdp tables=user01.TEST01 exclude=index;
Export: Release 11.2.0.1.0 - Production on Sun Nov 16 17:52:12 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_EXPORT_TABLE_01": /******** AS SYSDBA directory=EXP_DIR logfile=test01.table.expdp.log dumpfile=test01.table.expdp tables=user01.TEST01 exclude=index
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 702 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER01"."TEST01" 448.4 MB 4643904 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/data/oracle/oradata/exp_dir/test01.table.expdp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 17:52:57
所用时间：17:52:57-17:52:12=45s
4 单独导入表(记录时间)
[oracle@localhost exp_dir]$ echo "system/gaoxu" | impdp directory=EXP_DIR dumpfile=test01.table.expdp logfile=test01.impdp.table.log;
Import: Release 11.2.0.1.0 - Production on Sun Nov 16 18:05:45 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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=EXP_DIR dumpfile=test01.table.expdp logfile=test01.impdp.table.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER01"."TEST01" 448.4 MB 4643904 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:06:34
所用时间：18:06:34-18:05:45=48s
5 重建索引(记录时间)
USER01@aaron> set timing on
USER01@aaron> create index test01_objid_idx on test01(object_id);
Index created.
Elapsed: 00:00:40.38
总共用时：45s+48s+40s=133s
总结：虽然本实验的效果并不是很明显（135秒和133秒），作者建议，在使用数据泵做数据迁移的时候，如果原表中有大量的索引，最好首先单独只导出数据本身，而不导出相关索引，在数据导入后可以重新创建索引，这样会节省出一部分的时间。可以在原库使用 dbms_metadata.get_ddl 包来获取索引的创建SQL语句。
