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




其实这个任务对于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


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.




连接到: 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


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.




连接到: 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


Copyright (c) 2003, 2005, Oracle.  All rights reserved.


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


Copyright (c) 2003, 2005, Oracle.  All rights reserved.


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(*)


----------


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


Copyright (c) 2003, 2005, Oracle.  All rights reserved.


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(*)


----------


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


Copyright (c) 2003, 2005, Oracle.  All rights reserved.


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(*)


----------


       95


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html