6.导入单个分区
[Oracle@node1 ~]$impdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log \
> tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:13:28 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log
tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace--*/
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TB_PT":"SAL_OTHER"71.63 KB2875 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:13:33
SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT';
INDEX_NAMESTATUSPAR
------------------------------ -------- ---
TB_PT_IND1VALIDNO
TB_PT_LOCAL_IDXN/AYES
从上面的导入情况可以看出,尽管执行了truncate partition,然而使用impdp导入工具,并且使用参数table_exists_action=replace
可以避免使用imp导入时唯一和主键索引需要重建的问题。注意,如果没有使用table_exists_action=replace参数,将会收到ORA-39151
错误,如下
ORA-39151: Table "SCOTT"."TB_PT" exists. All dependent metadata and data will be skipped due to
table_exists_action of skip
7.导入整个表
[oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log \
> tables=tb_pt skip_unusable_indexes=y table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:17:35 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log
tables=tb_pt skip_unusable_indexes=y table_exists_action=replace--*/
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TB_PT":"SAL_OTHER"71.63 KB2875 rows
. . imported "SCOTT"."TB_PT":"SAL_11"12.54 KB298 rows
. . imported "SCOTT"."TB_PT":"SAL_12"14.22 KB366 rows
. . imported "SCOTT"."TB_PT":"SAL_13"14.18 KB365 rows
. . imported "SCOTT"."TB_PT":"SAL_14"14.18 KB365 rows
. . imported "SCOTT"."TB_PT":"SAL_15"14.19 KB365 rows
. . imported "SCOTT"."TB_PT":"SAL_16"14.23 KB366 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:17:40
五、参数skip_unusable_indexes的作用
SQL> show parameter skip
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexesbooleanTRUE
该参数允许在导入分区数据时延迟对索引的处理,即先将数据导入,导入后再来重建索引分区。
在命令行导入中未指定导入参数skip_unusable_indexes时,则对于索引相关的问题,根据数据库初始化参数的值来确定。
在命令行导入中如果指定了参数skip_unusable_indexes时,则该参数的值优先于数据库初始化参数的设定值。
skip_unusable_indexes=y对unique index不起作用,因为此时的unique index扮演者constraint的作用,所以在insert数据时index必须被
更新。
对于单个分区导入时PK,unique index的处理,必须先重建索引然后进行导入。
使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题,即ORA-01502错误。