資料泵對已經存在的表載入索引

一個朋友提到一個資料泵匯入的問題，在表存在的情況下，不刪除表，且匯入表的資料和索引。

 

 

其實這個任務對於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

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

連線到: 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

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

連線到: 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

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

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

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

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

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

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

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

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