Considerations for Oracle expdp/impdp data migration

Source: Internet
Author: User



For details about how to use the expdp/impdp command, refer:

Oracle expdp/impdp example



Comparison between exp/IMP and expdp/impdp and some optimization items in use



Example of Oracle client using expdp/impdp






1. User-based migration

You only need to create the relevant tablespace. You do not need to create a user. During the import process, the data pump automatically creates relevant users and grants permissions. Even if we do not know the user password, the data pump can import the original password. This can simplify our work.


For example:

Expdp directory = backupdumpfile = Dave. dmp logfile = Dave. Log schemas = user1, user2, user3;

Impdp directory = backupdumpfile = Dave. dmp logfile = Dave. Log schemas = user1, user2, user3;



2. When using a data pump, if its parallel feature is used, the number of parallel lines must be the same as the number of corresponding dump files.

For example:

Expdp full = ydirectory = backup dumpfile = Dave _ % u. dmp parallel = 4

Expdp creates four files for parallel: dave_01.dmp, Dave _ 02.dmp, Dave _ 03.dmp, and Dave _ 04.dmp. Each process has one file. In this way, the size of each file varies with the process. A file may be large but small. To solve this problem, set the filesize parameter. To specify the maximum value of each file. In this way, when a file reaches the maximum value, a new file will be created.


For example:

Expdp full = ydirectory = dump dumpfile = Dave _ % u. dmp parallel = 4 filesize = 1g


The exported dump file is related to paralle, so the import is also related. Paralle must be smaller than the number of dump files. If paralle is greater than the number of dump files, the performance will not be improved because the process that exceeds the limit cannot obtain the files.



3. During migration, pay attention to two types of objects

Generally, pay attention to the following information before migration:

(1) Total number of objects to be migrated and detailed information

SQL> select count (*) from all_objectswhere owner in ('user1', 'user2 ')


SQL> select object_type, count (1) as "num" from all_objects where owner in ('user1', 'user2') group byobject_type;


Object_type num


Sequence 85

Procedure 74

Package 6

Package body 6

Trigger 5

Table 219

Index 229

Synonym 1

View 4

Function 3

Type 2


11 rows selected.



(2) Invalid object information

SQL> select count (*) from all_objectswhere owner in ('user1', 'user2') and status = 'invalid ';

SQL> select owner, object_name fromall_objects where owner in ('user1', 'user2') and status = 'invalid ';


After the migration is complete, compare the number of objects before and after migration and the number of invalid objects. The two objects that need attention here are dblink and job. In my tests, these two types of objects are basically not automatically imported. You need to manually re-create these objects after the import is complete.


View dblink information:

SQL> select owner, object_name, object_type from dba_objects where object_type = 'databaselink ';


View job information:

SQL> select count (*) from dba_jobs where schema_user in ('user1', 'user2 ');








Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!


Skype: tianlesoftware






------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Group dba6: 158654907 dba7: 172855474

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.