Shulou(Shulou.com)05/31 Report--

Editor to share with you how to partition IMPDP by scope, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The ESB_MSG_LOG table is partitioned by range. When the historical partition is deleted, 201707 more partitions are deleted.

Restore and export 201707 partitions by backup and offsite

Expdp system/manager TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp

ESB_MSG_LOG now has 201708 and 201709 partitions. Will importing 201707 affect ESB_MSG_LOG?

Impdp system/123 TABLES=esbadmin.ESB_MSG_LOG:ESBLOG201707 directory=DATA_PUMP_DIR dumpfile=ESBLOG201707.dmp

Import error report

[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

The table does not use Interval, and now there is no 201707 partition information in the table, so you must manually create 201707 partitions. Otherwise, according to the partition condition PARTITION ESBLOG201708 VALUES LESS THAN ('2017-09-01 00 00.000'), the following statement directly imports all the contents of 201707 partitions into 201708 partitions

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 0015 0000')

ORA-14074:partition bound must collate higher

Partition by range, so the oldest and smallest partition is deleted, basically can not come back, the risk is so high.

The above is all the content of the article "how to partition IMPDP by range". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.