Data pump export/import EXPDP/IMPDP

Oracle 10g introduces the data pump provides a server-based extract and recovery utility , and data pump has a significant improvement in architecture and functionality compared to traditional export and import utilities. Data pump allows you to stop and restart jobs, view the status of running jobs, and limit the data you import and export.

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

One, the following is a few advantages of Data Pump Introduction:

1. The data pump (datapump) All work has the database instance to complete, the database can handle these work in parallel, can not only through the establishment multiple data pump work process to read/write the data which is being exported/the import, also may establish the parallel i/ o servers to read or insert data more quickly , resulting in a single process bottleneck being completely resolved.

2. Through the data pump, previously through the Exp/imp mainly based on the Client/server logical backup mode to the server side of the fast backup , the data pump mainly work on the server side , can be in parallel mode to quickly load or unload data, And the degree of parallelism can be adjusted during the run to speed up or reduce resource consumption.

Second, the steps are as follows

1. Create a Directory

Data pump requires the creation of a directory for the files and log files to be created and read, this parameter is used to define a directory, the previous mentioned data pump mainly work on the server side, export files need to write out to the server side local directory, This directory is the corresponding server-side directory. 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 you begin the operation, and that the user who released the Create Directory command needs the system permissions of Create Anydirectory.

A directory named Test is created and the system user is granted read/write access to this directory.

Create a directory and give permissions:

-- Create an export directory Create  as ' d:\test '  -- authorizing the user grantread on to system;

View the two views of the directory that you have created in the database:

-- View the directory already created in the database SELECT *  from SELECT * from dba_directories;     

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

2. Data Pump Export

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

1) Export of individual user scenarios

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

2) Database full-Library export

EXPDP [user name]/[Password]@[host character channeling] full=y directory=test dumpfile=x.dmp logfile=x.log

3. Data Pump Import

According to the above export method:

Single User scenario import

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

Database Full Library Import

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

4. Delete Directory

Note: The location of the directory

Drop directory Test Deletes directories

Data pump export/import EXPDP/IMPDP