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

原创

Oracle小混子 2013-06-09 10:29:39 ©著作权

文章标签 Oracle Oracle数据泵 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Oracle小混子的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

其实这个任务对于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: Release9.2.0.4.0 - Production on星期三6月2 15:12:26 2010

连接到: Oracle9i Enterprise Edition Release9.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: Release9.2.0.4.0 - Production on星期三6月2 15:13:10 2010

连接到: Oracle9i Enterprise Edition Release9.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: Release10.2.0.3.0 - 64bit Production on星期三, 02 6月, 2010 15:18:59

Connected to: Oracle Database10gEnterprise Edition Release10.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: Release10.2.0.3.0 - 64bit Production on星期三, 02 6月, 2010 15:20:26

Connected to: Oracle Database10gEnterprise Edition Release10.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(*)

----------

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: Release10.2.0.3.0 - 64bit Production on星期三, 02 6月, 2010 15:21:32

Connected to: Oracle Database10gEnterprise Edition Release10.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(*)

----------

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: Release10.2.0.3.0 - 64bit Production on星期三, 02 6月, 2010 15:23:06

Connected to: Oracle Database10gEnterprise Edition Release10.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(*)