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

This article mainly explains "Oracle 11g impdp first data and then index import method", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Oracle 11g impdp first data then index import method is what" it!

Scene:

Database expdp,impdp export and import, because the index import time is long, it is easy to lead to abnormal termination to form an uncontrollable situation. Therefore, the method of importing data first, and then importing index and constraint is adopted. Improve the accuracy of data import.

Environment:

Database: oracle 11.2.0.4

Operating system: aix 6.1

Dump files are about 30g in size and 200g when uncompressed.

1. The flow of the operation is as follows:

1.1 user Export

# # eliminate useless tables, reduce the pressure of export and import, and improve efficiency. It is quite obvious that you should use the

# # eliminate dblink, reduce the risk of misoperation, often pre-production, the test environment is very similar to production, in order to avoid misoperation. It is recommended to exclude dblink export and import and replace # # by artificial reconstruction.

# # compressed compression is effective. It is recommended to use it when CPU resources are sufficient.

# # recommended in cluster=no,RAC environment

Expdp system/**** directory=dump_dir dumpfile=monthend_prd_20200312_%U.dmp schemas=BMC_prd parallel=12 cluster=no exclude=table:\ "IN\ (\ 'BIG_TABLE1\',\ 'BIG_LOG1\',\ 'BIG_HIS_TAB1\') exclude=DB_LINK logfile=expdp_monthend_prd_20200312.log compression=ALL

# # the main usage is to use exclude and include, exclude is used when excluding index, and include## must be used when importing index

1.2 user data Import

Impdp system/**** directory=DB_DUMP parallel=4 dumpfile=monthend_prd_20200312_%u.dmp logfile=monthend_prd_20200312_%u_2.log remap_schema=BMC_prd:BMC_PREPROD2 exclude=index exclude=constraint

1.3user index and constraint imports

Impdp system/**** directory=DB_DUMP parallel=4 dumpfile=monthend_prd_20200312_%u.dmp logfile=monthend_prd_20200312_%u_3.log remap_schema=BMC_prd:BMC_PREPROD2 include=index include=constraint

# # it is interesting to import the index into impdp here. I propose log analysis for you. Go directly to the import index. # #

******

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

two。 The efficiency comparison (measured in terms of time) is as follows:

2.1 time consumed when data and indexes were not imported separately: 08:24:06

2.2 time consumed for separate import of data and indexes: 08:04:44

Data 01:17:01

Index 06:47:43

At this point, I believe you have a deeper understanding of "Oracle 11g impdp first data and then index import method". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.