ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据泵造成的数据损失

数据泵造成的数据损失

原创 Linux操作系统 作者:yangtingkun 时间:2012-03-11 23:03:05 0 删除 编辑

客户尝试导入一个分区表的个别几个分区,结果却损失了整个分区表的数据。

 

 

下面通过一个例子来在线这个问题:

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有关,因此只要是涉及到数据库修改的,还是要谨慎处之。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-718452/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10386256