Data pump export/import EXPDP/IMPDP

Data Pump export/import EXPDP/IMPDP

Oracle 10g introduces the data pump provides a server-based database extraction and Recovery utility , and the data pump significantly improves architecture and functionality compared to the traditional export and import utilities. Data pump allows you to stop and restart the job, view the status of the running job, and restrict the import and export of the information.

Note: Data pump files are incompatible with traditional exp/imp data dump files.

First, the following is the data pump several advantages of the introduction:

1. Data Pump (datapump) All the work has a database instance to complete, the database can handle these tasks in parallel, not only through the establishment of a number of data pump work process to read/write data being exported/imported, can also establish parallel i/ o Server to read or insert data faster , the single process bottleneck is completely resolved.

2. Through the data pump, formerly through the Exp/imp mainly based on client/server logical backup to the server side of the fast backup , the data pump is mainly in the server side , can be quickly loaded or unloaded data in parallel way, And you can adjust the degree of parallelism during the run to speed up or reduce resource consumption.

Second, the steps are as follows

1. Create Directory

The data pump requires the creation of a directory for the file and log files that will be created and read, which is used to define a directory, which has previously been mentioned that the data pump works primarily on the server side, and that the export file needs to be written out to the server-side local directory. This directory is the directory of the corresponding server side. The user who will access the data pump file must have read/write access to the directory.

Note: Verify that the external directory exists before starting the operation, and that the user who issued the Create Directory command needs to have system permissions for the Create Anydirectory.

The following gives a directory named Test and grants the system user access to this directory read/write permission.

To create a directory and grant permissions:

Sql> Create directory TEST as ' d:\test '; sql> Grant read,write on directory TEST to system;

View two views of a directory that has been created in the database:

SELECT * from All_directories; SELECT * from Dba_directories;

Then create the folder manually under the target disk D:\test

2. Data Pump Export

There are many ways to export data pump, here we only introduce two kinds:

Ø Single user scheme export

EXPDP [username//Password]@[host character channeling] schemas=[username] directory=test dumpfile=x.dmp logfile=x.log

Ø Database Whole library export

EXPDP [username]/password]@[host character channeling] full=y directory=test dumpfile=x.dmp logfile=x.log

3. Data Pump Import

Export by:

Ø Single user scheme import

IMPDP [username//Password]@[host character channeling] schemas=[username] directory=test dumpfile=x.dmp logfile=x.log ignore=y

Ø Database Whole library Import

IMPDP [username]/password]@[host character channeling] full=y directory=test dumpfile=x.dmp logfile=x.log

Note: Location of directory

Drop Directory Test Delete directory