Oracle Data Pump Import and Export summary

Oracle Data Pump (IMPDP/EXPDP) Import and Export summary

Oracle Data Pump Import and export is one of the basic techniques commonly used in daily work, it is more efficient than traditional logic import and export, which is more suitable for the large number of database objects, because my daily operations database objects are thousands of, more tens of thousands of or even hundreds of thousands of, so the traditional exp/imp will be very time-consuming , and the data pump mode is thus off, the following detailed summary of the use of data pumps, hoping to bring help to beginners.

First, new logical directory

It is best to create a logical directory with administrators such as system, and Oracle does not automatically create the actual physical directory "D:\oracleData" (be sure to create this directory manually ), only to define the logical path Dump_dir;

Sql> Conn System/[email protected] as SYSDBA;

Sql>create directory Dump_dir as ' D:\oracleData ';

Second, view the Administrator directory (and also see if the operating system exists in the directory, because Oracle does not care if the directory exists, if it does not exist, error)

Sql>select * from Dba_directories;

Third, export data with EXPDP

1) exporting users and their objects
EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dump_dir;

2) export the specified table
EXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dump_dir;

3) Guided by query criteria
EXPDP Scott/[email protected] directory=dump_dir dumpfile=expdp.dmp tables=empquery= ' where deptno=20 ';

4) According to the Table space Guide
EXPDP System/[email protected] Directory=dump_dir dumpfile=tablespace.dmptablespaces=temp,example;

5) Guide the entire database
EXPDP System/[email protected] Directory=dump_dir dumpfile=full.dmp full=y;

Iv. Importing data with IMPDP

Before you formally import the data, make sure that the user you want to import already exists, and if it does not exist, first create a new user with the following command

--Create TABLE space
Create tablespace tb_name datafile ' D:\tablespace\tb_name.dbf ' size 1024m autoextend on;

--Create User
Create user user_name identified by a123456a default tablespace tb_name temporary tablespace TEMP;

--Authorization to the user

Sql>grant read,write on the directory Dump_dir to user_name;

Sql>grant dba,resource,unlimited tablespace to user_name;

1) Import user (from user Scott to user Scott)
IMPDP Scott/[email protected] Directory=dump_dir dumpfile=expdp.dmp Schemas=scott;

2) Import table (Import the table dept and EMP from the Scott user into the system user)
IMPDP System/[email protected] Directory=dump_dir dumpfile=expdp.dmptables=scott.dept,scott.emp Remap_schema=scott: System

3) Import Table space
IMPDP System/[email protected] Directory=dump_dir dumpfile=tablespace.dmp tablespaces=example;

4) Import the database
impdb System/[email protected] Directory=dump_dir dumpfile=full.dmp full=y;

5) Append Data
IMPDP System/[email protected] Directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action

The above is used in the actual work in the daily job, hope can give you get help.

Oracle Data Pump Import and Export summary