一个朋友提到一个数据泵导入的问题，在表存在的情况下，不删除表，且导入表的数据和索引。

其实这个任务对于imp很简单，因为imp的工作方式就是如此。

SQL> CREATE TABLE T_EXP
2 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T_EXP_ID
2 ON T_EXP(ID);

索引已创建。

SQL> INSERT INTO T_EXP
2 SELECT ROWNUM, TNAME
3 FROM TAB;

已创建72行。

SQL> COMMIT;

提交完成。

SQL> HOST exp test/test file=t_exp.dmp buffer=2048000 tables=t_exp

Export: Release 9.2.0.4.0 - Production on 星期三 6月 2 15:12:26 2010

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径 ...
. . 正在导出表 T_EXP 72 行被导出
在没有警告的情况下成功终止导出。

SQL> DROP INDEX IND_T_EXP_ID;

索引已丢弃。

SQL> HOST imp test/test file=t_exp.dmp buffer=2048000 tables=t_exp ignore=y

Import: Release 9.2.0.4.0 - Production on 星期三 6月 2 15:13:10 2010

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将TEST的对象导入到 TEST
. . 正在导入表 "T_EXP" 72行被导入
成功终止导入，但出现警告。

SQL> SELECT COUNT(*) FROM T_EXP;

COUNT(*)
----------
144

SQL> SELECT INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T_EXP';

INDEX_NAME
------------------------------
IND_T_EXP_ID

而impdp的默认工作并非如此，如果监测到表存在，impdp会跳过索引的创建：

SQL> CREATE TABLE T_EXP (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T_EXP
2 SELECT ROWNUM, TNAME
3 FROM TAB;

95 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX IND_T_EXP_ID
2 ON T_EXP (ID);

Index created.

下面执行导出：

[oracle@yans1 ~]$ expdp test/test directory=d_output dumpfile=t_exp.dp tables=t_exp

Export: Release 10.2.0.3.0 - 64bit Production on 星期三, 02 6月, 2010 15:18:59

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_exp.dp tables=t_exp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
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 "TEST"."T_EXP" 6.890 KB 95 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_exp.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:19:27

清除数据，并删除索引：

SQL> DROP INDEX IND_T_EXP_ID;

Index dropped.

SQL> TRUNCATE TABLE T_EXP;

Table truncated.

执行impdp导入：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate

Import: Release 10.2.0.3.0 - 64bit Production on 星期三, 02 6月, 2010 15:20:26

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TEST"."T_EXP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_EXP" 6.890 KB 95 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "TEST"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 15:31:28

检查索引和数据：

SQL> SELECT COUNT(*) FROM T_EXP;

COUNT(*)
----------
95

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'T_EXP';

no rows selected

数据虽然导入了，但是索引没有创建。不过要解决这个问题也很简单，通过INCLUDE就可以解决这个问题：

SQL> TRUNCATE TABLE T_EXP;

Table truncated.

加上INCLUDE=INDEX执行导入：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate include=index

Import: Release 10.2.0.3.0 - 64bit Production on 星期三, 02 6月, 2010 15:21:32

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate include=index
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 15:21:34

可以看到，这次似乎没有导入数据，检查一下：

SQL> SELECT COUNT(*) FROM T_EXP;

COUNT(*)
----------
0

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'T_EXP';

INDEX_NAME
------------------------------
IND_T_EXP_ID

果然，虽然索引导入了，但是数据没有导入。

不过这就更简单了，通过INCLUDE=INDEX和INCLUDE=TABLE_DATA，就可以解决这个问题了：

SQL> DROP INDEX IND_T_EXP_ID;

Index dropped.

删除索引，执行导入：

[oracle@yans1 ~]$ impdp test/test directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate include=index include=table_data

Import: Release 10.2.0.3.0 - 64bit Production on 星期三, 02 6月, 2010 15:23:06

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=d_output dumpfile=t_exp.dp tables=t_exp table_exists_action=truncate include=index include=table_data
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_EXP" 6.890 KB 95 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 15:23:08

最后检查一下是否成功：

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'T_EXP';

INDEX_NAME
------------------------------
IND_T_EXP_ID

SQL> SELECT COUNT(*) FROM T_EXP;

COUNT(*)
----------
95

ITPub博客

数据泵对已经存在的表加载索引

最新文章