IMPDP导入范围分区中最旧最小的分区_惨痛教训
ESB_MSG_LOG表按范围分区，删除历史分区时，多删了201707分区
通过备份再异机恢复并导出201707分区
expdp system/manager TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
本机再导入
ESB_MSG_LOG现在还有201708、201709两个分区，导入201707会不会影响到ESB_MSG_LOG
impdp system/123 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
导入报错
[oracle@dwdb log]$ impdp system/123 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
Import: Release 11.2.0.4.0 - Production on Tue Sep 5 21:35:26 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ESBADMIN"."ESB_MSG_LOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Sep 5 21:35:28 2017 elapsed 0 00:00:01
表没有使用Interval，现在表中已经没有201707没有分区信息，所以必须去手工建立201707的分区，否则按分区条件PARTITION ESBLOG201708 VALUES LESS THAN ('2017-09-01 00:00:00.000')，下面的语句直接把201707分区内容全部导入到201708分区了
impdp system/dw54321 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp table_exists_action=append
ALTER TABLE ESB_MSG_LOG ADD PARTITION ESBLOG201707 VALUES LESS THAN ('2017-08-01 00:00:00.000')
ORA-14074：partition bound must collate higher
按范围分区，所以最旧最小的分区删除了，基本就回不来了，风险好高啊
通过备份再异机恢复并导出201707分区
expdp system/manager TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
本机再导入
ESB_MSG_LOG现在还有201708、201709两个分区，导入201707会不会影响到ESB_MSG_LOG
impdp system/123 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
导入报错
[oracle@dwdb log]$ impdp system/123 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
Import: Release 11.2.0.4.0 - Production on Tue Sep 5 21:35:26 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ESBADMIN"."ESB_MSG_LOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Sep 5 21:35:28 2017 elapsed 0 00:00:01
表没有使用Interval，现在表中已经没有201707没有分区信息，所以必须去手工建立201707的分区，否则按分区条件PARTITION ESBLOG201708 VALUES LESS THAN ('2017-09-01 00:00:00.000')，下面的语句直接把201707分区内容全部导入到201708分区了
impdp system/dw54321 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp table_exists_action=append
ALTER TABLE ESB_MSG_LOG ADD PARTITION ESBLOG201707 VALUES LESS THAN ('2017-08-01 00:00:00.000')
ORA-14074：partition bound must collate higher
按范围分区，所以最旧最小的分区删除了，基本就回不来了，风险好高啊
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2144550/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
