資料泵造成的資料損失

客戶嘗試匯入一個分割槽表的個別幾個分割槽，結果卻損失了整個分割槽表的資料。

下面透過一個例子來線上這個問題：

SQL> CREATE TABLE T_PART PARTITION BY RANGE (CREATED)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2-1', 'YYYY-MM-DD')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE('2012-3-1', 'YYYY-MM-DD')),
5 PARTITION P4 VALUES LESS THAN (TO_DATE('2012-4-1', 'YYYY-MM-DD')),
6 PARTITION PMAX VALUES LESS THAN (MAXVALUE))
7 AS SELECT * FROM DBA_OBJECTS;

Table created.

SQL> host expdp test directory=d_output dumpfile=t_part.dp logfile=t_part.log tables=t_part:p3, t_part:p4

Export: Release 11.2.0.3.0 - Production on Tue Mar 13 12:17:00 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=d_output dumpfile=t_part.dp logfile=t_part.log tables=t_part:p3, t_part:p4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 16 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T_PART":"P3" 29.89 KB 193 rows
. . exported "TEST"."T_PART":"P4" 65 KB 425 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_part.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 12:17:17

客戶嘗試匯入幾個分割槽，但是這些分割槽並不為空，於是在匯入時指定了錯誤的TABLE_EXISTS_ACTION引數為REPLACE：

SQL> host impdp test directory=d_output dumpfile=t_part.dp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace

Import: Release 11.2.0.3.0 - Production on Tue Mar 13 14:51:55 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=d_output dumpfile=t_part.dp logfile=t_part_imp.log tables=t_part:p3 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_PART":"P3" 29.89 KB 193 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 14:52:00

雖然匯入的時候僅指定了一個分割槽，但是Oracle並不會對這一個分割槽執行TRUNCATE操作，而是將整個表DROP掉，然後利用資料泵中的源資料重建。

SQL> SELECT COUNT(*) FROM T_PART PARTITION (P1);

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

SQL> SELECT COUNT(*) FROM T_PART PARTITION (P2);

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

SQL> SELECT COUNT(*) FROM T_PART PARTITION (P3);

COUNT(*)
----------
193

SQL> SELECT COUNT(*) FROM T_PART PARTITION (P4);

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

SQL> SELECT COUNT(*) FROM T_PART PARTITION (PMAX);

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

這個表中只有匯入的分割槽記錄存在，原則上impdp對應的資料泵匯出檔案中的記錄也都是可以恢復的，但是表中其他的分割槽資料則完全丟失。

而且Oracle在刪除的時候還使用PURGE選項，使得分割槽表在刪除的時候並沒有被放到回收站中，而是直接從資料庫中被清除。從這一點上講，Oracle應該去掉PURGE語句，或者至少給出一個選項，畢竟DROP TABLE對於系統來說有風險的。

最近碰到兩三次的問題都是和imp以及impdp有關，因此只要是涉及到資料庫修改的，還是要謹慎處之。