Oracle Data Pump Usage Explained

Things to keep in mind when using EXPDP and IMPDP data pumps:

Exp and IMP are client tool programs that can be used either on the client or on the server side.

EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and not on the client side.

IMP is only available for exp exported files, not for EXPDP export files, IMPDP only for EXPDP exported files, not for exp export files.

EXPDP or IMPDP command, you can temporarily not indicate the username/password @ instance name as identity, and then follow the prompts to enter, such as:


EXPDP Schemas=scott dumpfile=expdp.dmp Directory=dir;
Environment
Windows2003sp2

Oracle11gr1
Using EXPDP and IMPDP
1. Command line open Sqlplus
Sqlplus/nolog
Conn/as SYSDBA
2. Create a logical directory that does not create a real directory in the operating system (preferably manually first), preferably created by an administrator such as administrator.
Create directory dir as ' D:\dump '; Dir name can be arbitrarily named need to create d:\dump manually
3. View the Management Manager directory (while viewing the presence of the operating system, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)
SELECT * from Dba_directories;
4. Give the Scott user permission to operate on the specified directory, preferably by an administrator such as system.
Grant Read,write on directory dir to Scott;
5. Export Data

1) According to the user guide

EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dir logfile=expdp.log

2) Parallel Process parallel

EXPDP Scott/[email protected] Directory=dir dumpfile=scott3.dmp parallel=40 job_name=scott3

3) According to the table name guide

EXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dir;

4) Guided by query criteria

EXPDP Scott/[email protected] directory=dir dumpfile=expdp.dmp tables=emp query= ' WHERE deptno=20 ';

5) According to the Table space Guide

EXPDP System/manager directory=dir dumpfile=tablespace.dmp tablespaces=temp,example;

6) Guide the entire database

EXPDP System/manager directory=dir dumpfile=full.dmp full=y;
6. Restore Data

1) leads to the specified user

IMPDP Scott/[email protected] Directory=dir dumpfile=expdp.dmp Schemas=scott logfile=impdp.log

2) Change the owner of the table

IMPDP system/manager directory=dir dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system;

3) Import Table space

IMPDP System/manager directory=dir dumpfile=tablespace.dmp tablespaces=example;

4) Import the database

impdb System/manager directory=dump_dir dumpfile=full.dmp full=y;

5) Append Data

IMPDP system/manager directory=dir dumpfile=expdp.dmp schemas=system table_exists_action



****************************************************************************************
Linux Environment (RHEL5)
oracle10g and oracle11g
Using EXPDP and IMPDP
1. Command line open Sqlplus
Sqlplus/nolog
Connect system/oracle
2. Create a logical directory, which does not create a real directory in the operating system (finally manually built first), preferably created by an administrator such as system.
Create directory dpdata1 as '/home/oracle/dump ';
3. View the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)
SELECT * from Dba_directories;
4. Give the Scott user permission to operate on the specified directory, preferably by an administrator such as system.
Grant Read,write on directory dpdata1 to Scott;
5. Exporting data
1) According to the user guide
EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;
2) According to the Table space Guide
EXPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
3) Guide the entire database
EXPDP System/manager directory=dpdata1 dumpfile=full.dmp full=y;
6. Restore Data
1) leads to the specified user
IMPDP Scott/tiger directory=dpdata1 dumpfile=expdp.dmp Schemas=scott;
3) Import Table space
IMPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=example;
4) Import the database
impdb System/manager directory=dump_dir dumpfile=full.dmp full=y;
*************************************************************************************
EXPDP/IMPDP use of different table spaces for different users
Assuming that the default tablespace for a user is a, export all user a data:

Sql> Conn/as SYSDBA
Sql> Create directory dir as '/home/oracle/';
Sql> Grant Read,write on the directory dir to system;

$EXPDP system/oracle directory=dir dumpfile=data.dmp schemas=a logfile=data.log

Impdp
Import all data for a user to B, and convert table space A to B:

Sql> Conn Sys/as SYSDBA
Sql> Create directory dir as '/home/oracle/';
Sql> Grant Read,write on the directory dir to system;

$IMPDP system/oracle directory=dir dumpfile=data.dmp remap_tablespace=a:b remap_schema=a:b logfile=data.log



Explain:

Remap_schema=a:b to convert the schema of the data from A to B

Remap_tablespace=a:b to convert the tablespace of data from A to B


NOTE: If Oracle is 10g, the Add parameter exclude=table_statistics option will filter the Table_statistics object. Otherwise there will be data pump import table_statistics long wait, with IMPDP import, check table_statistics wait n long time


Summary: You do not need to create a B user when you execute IMPDP, automatically create and rename User A to B (all permissions for a), automatically set the default tablespace to converted Tablespace B when importing. If more than one table space is required for conversion, use multiple remap_tablespace= sources: target fields.

This method is limited to supporting versions of oracle10g or more.


Table_statistics long-time waiting problem in data pump import
Use IMPDP to import, check table_statistics wait for n long time.

Online said this is a bug in Oracle 10G IMPDP, if the command defines the Remap_schema key, that is, the original user name and the imported user name is inconsistent, then this problem.
The workaround provided online is to have the user name of the import and export consistent, or to filter the Table_statistics object through the Exclude=table_statistics option on import.

Oracle Data Pump Usage Explained