EXPDP 应用
1) 准备工作

——创建目录：

[oracle@wang ~]$ mkdir datadump

[oracle@wang ~]$ ls

base.ctl base_data.bad base_data.dat base.log datadump

[oracle@wang ~]$ cd datadump/

[oracle@wang datadump]$ ls

[oracle@wang datadump]$ pwd

/home/oracle/datadump

[oracle@wang datadump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 24 08:25:02 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

——创建目录对象，并授权给要使用的用户：（因为DATA PUMP 是服务端工具，导出文件必须生成在服务端）

SYS@ORA11GR2>create or replace directory dir_dp as '/home/oracle/datadump';

Directory created.

SYS@ORA11GR2>grant read,write on directory dir_dp to scott;

Grant succeeded.

SYS@ORA11GR2>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wang datadump]$

2) 导出表中指定行（表模式导出）

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=emp30.dmp tables=emp query="'where deptno = 30'";

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 08:29:32 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dir_dp dumpfile=emp30.dmp tables=emp query='where deptno = 30'

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.25 KB 6 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/datadump/emp30.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 24 08:29:59 2016 elapsed 0 00:00:23

——查看导出生成的文件：

[oracle@wang ~]$ cd /home/oracle/datadump/

[oracle@wang datadump]$ ls

emp30.dmp export.log

[oracle@wang datadump]$

3) parfile 导出表中指定行（利用parfile参数文件格式导出数据，表模式导出）

[oracle@wang datadump]$ vi par.txt

userid=scott/tiger

directory=dir_dp

dumpfile=emp30_2.dmp

logfile=emp30_2.log

tables=emp

query='where deptno = 30'

~

"par.txt" [New] 6L, 115C written

[oracle@wang datadump]$

验证：

[oracle@wang datadump]$ ls

emp30.dmp export.log par.txt

——执行参数文件：

[oracle@wang datadump]$ expdp parfile=par.txt

(注意不要在empdp/impdp导入导出命令或参数文件中最后结尾加；)

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 08:40:35 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** parfile=par.txt

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.25 KB 6 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/datadump/emp30_2.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 24 08:40:53 2016 elapsed 0 00:00:17

——验证：

[oracle@wang datadump]$ ls

emp30_2.dmp emp30_2.log emp30.dmp export.log par.txt

4) 导出表（不加query限制条件）

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=emp.dmp tables=emp

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 08:43:33 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dir_dp dumpfile=emp.dmp tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/datadump/emp.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 24 08:43:57 2016 elapsed 0 00:00:21

——验证：

[oracle@wang datadump]$ ls

emp30_2.dmp emp30.dmp export.log

emp30_2.log emp.dmp par.txt

[oracle@wang datadump]$

5) 导出 schema（用户模式导出数据）

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=scott.dmp schemas=scott

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 08:49:11 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=dir_dp dumpfile=scott.dmp schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT" 5.929 KB 4 rows

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . exported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/datadump/scott.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Sep 24 08:50:35 2016 elapsed 0 00:01:20

——验证：

[oracle@wang datadump]$ ls

emp30_2.dmp emp30.dmp export.log scott.dmp

emp30_2.log emp.dmp par.txt

6) 导出表空间（表空间模式导出）
（注：要使用 DBA 角色的用户导出，否则只导出本 schema 的对象）

[oracle@wang datadump]$ expdp system/oracle directory=dir_dp dumpfile=ts.dmp logfile=ts.log tablespaces=users

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 09:00:29 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=dir_dp dumpfile=ts.dmp logfile=ts.log tablespaces=users

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.062 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER"

. . exported "OE"."PURCHASEORDER" 243.9 KB 132 rows

. . exported "HR"."FT" 16.80 KB 107 rows

. . exported "OE"."CATEGORIES_TAB" 14.15 KB 22 rows

. . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.50 KB 288 rows

. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.585 KB 21 rows

. . exported "SCOTT"."DEPT" 5.929 KB 4 rows

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . exported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:

/home/oracle/datadump/ts.dmp

Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sat Sep 24 09:02:01 2016 elapsed 0 00:01:27

——验证：

[oracle@wang datadump]$ ls

emp30_2.dmp emp30.dmp export.log scott.dmp ts.log

emp30_2.log emp.dmp par.txt ts.dmp

[oracle@wang datadump]$

[oracle@wang datadump]$ du -h ts.dmp

836K ts.dmp

[oracle@wang datadump]$ du -h ts.log

4.0K ts.log

[oracle@wang datadump]$

7) 导出数据库（数据库导出模式）

[oracle@wang datadump]$ expdp system/oracle directory=dir_dp dumpfile=db.dmp
logfile=db.log full=y

——验证：

[oracle@wang datadump]$ ls

db.dmp dept6.dmp emp30_2.log emp.dmp par.txt ts.dmp

db.log emp30_2.dmp emp30.dmp export.log scott.dmp ts.log

[oracle@wang datadump]$

8) JOB_NAME（即指定导出操作的job_name）

会话 1：

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=dept6.dmp tables=dept job_name='t_expdp_job'

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 09:18:43 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."T_EXPDP_JOB": scott/******** directory=dir_dp dumpfile=dept6.dmp tables=dept job_name=t_expdp_job

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT" 5.929 KB 4 rows

Master table "SCOTT"."T_EXPDP_JOB" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.T_EXPDP_JOB is:

/home/oracle/datadump/dept6.dmp

Job "SCOTT"."T_EXPDP_JOB" successfully completed at Sat Sep 24 09:19:03 2016 elapsed 0 00:00:16

[oracle@wang datadump]$

[oracle@wang datadump]$ ls

db.dmp dept6.dmp emp30_2.log emp.dmp par.txt ts.dmp

db.log emp30_2.dmp emp30.dmp export.log scott.dmp ts.log

[oracle@wang datadump]$

会话 2： 在会话 1 运行的同时执行下面的 sql，如果会话 1 执行完毕后，会话 2 的语句则不返回任何信息。

SYS@ORA11GR2>select owner,table_name from dba_tables where table_name='T_EXPDP_JOB';

OWNER TABLE_NAME

------------------------------ --------------------------------------------

SCOTT T_EXPDP_JOB

SYS@ORA11GR2>select owner_name,job_name,job_mode from dba_datapump_jobs;

OWNER_NAME JOB_NAME JOB_MODE

----------------------------------------------------------------------------------------------

SCOTT T_EXPDP_JOB TABLE

(表示job_name的任务正在执行)

——过一会再查：

SYS@ORA11GR2>select owner_name,job_name,job_mode from dba_datapump_jobs;

no rows selected

SYS@ORA11GR2>select owner,table_name from dba_tables where table_name='T_EXPDP_JOB';

no rows selected（表示job_name的任务已经完成)

9) ATTACH（即管理正在进行的导出作业）

会话1：

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=dept7.dmp tables=dept job_name='t_expdp_job'

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 09:29:53 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."T_EXPDP_JOB": scott/******** directory=dir_dp dumpfile=dept7.dmp tables=dept job_name=t_expdp_job

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT" 5.929 KB 4 rows

Master table "SCOTT"."T_EXPDP_JOB" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.T_EXPDP_JOB is:

/home/oracle/datadump/dept7.dmp

Job "SCOTT"."T_EXPDP_JOB" successfully completed at Sat Sep 24 09:30:22 2016 elapsed 0 00:00:26

会话 2：当会话 2 开始执行的时候，那么会话 1 会暂定

[oracle@wang datadump]$ expdp scott/tiger attach=t_expdp_job

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 09:29:57 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: T_EXPDP_JOB

Owner: SCOTT

Operation: EXPORT

Creator Privs: FALSE

GUID: 3D37D4FA6AAA45FDE0530100007FC175

Start Time: Saturday, 24 September, 2016 9:29:56

Mode: TABLE

Instance: ORA11GR2

Max Parallelism: 0

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND scott/******** directory=dir_dp dumpfile=dept7.dmp tables=dept job_name=t_expdp_job

State: DEFINING

Bytes Processed: 0

Job Error Count: 0

Dump File: /home/oracle/datadump/dept7.dmp

bytes written: 4,096

Export> help

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

ADD_FILE

Add dumpfile to dumpfile set.

CONTINUE_CLIENT

Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT

Quit client session and leave job running.

FILESIZE

Default filesize (bytes) for subsequent ADD_FILE commands.

HELP

Summarize interactive commands.

KILL_JOB

Detach and delete job.

PARALLEL

Change the number of active workers for current job.

REUSE_DUMPFILES

Overwrite destination dump file if it exists [N].

START_JOB

Start or resume current job.

Valid keyword values are: SKIP_CURRENT.

STATUS

Frequency (secs) job status is to be monitored where

the default [0] will show new status when available.

STOP_JOB

Orderly shutdown of job execution and exits the client.

Valid keyword values are: IMMEDIATE.

Export> status

UDE-31626: operation generated ORACLE error 31626

ORA-31626: job does not exist

ORA-39086: cannot retrieve job information

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551

ORA-06512: at line 1

（因为会话1的导出任务已经结束）

10) EXCLUDE/INCLUDE 用法

? 语法
? 剔除指定对象：
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
? 包含指定对象：
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
object_type: table,sequence,view,procedure,package 等所有对象类型
name_claus:由 SQL 操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象
? 示例
? expdp：
expdp
SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"
? impdp：
impdp
SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"
注示例无法运行，需要转义，之所以要转义是因为，这些命令是在操作系统中执行的，操作系统中逗号、
括号等都有特定的含义，所以，如果要将其当做字符使用的话，那么需要进行转义
? 常用的过滤表达式
? 过滤所有的 SEQUENCE,VIEW
EXCLUDE=SEQUENCE,VIEW
? 过滤表对象 EMP,DEPT
EXCLUDE=TABLE:"IN ('EMP','DEPT')"
? 过滤所有的 SEQUENCE,VIEW 以及表对象 EMP,DEPT
EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')"
? 过滤指定的索引对象 IDX_NAME
EXCLUDE=INDEX:"= 'INDX_NAME'"
? 包含以 SP 开头的所有存储过程
INCLUDE=PROCEDURE:"LIKE 'SP%'"

?例 1 exclude操作：

导出 scott 用户下除了 emp 和 dept 表之外的所有表
[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=scott_all_tab.dmp schemas=scott exclude=table:\" in $\'EMP\',\'DEPT\'$\"

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 09:56:06 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=dir_dp dumpfile=scott_all_tab.dmp schemas=scott exclude=table:" in ('EMP','DEPT')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . exported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/datadump/scott_all_tab.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Sep 24 09:57:28 2016 elapsed 0 00:01:19

——验证：

[oracle@wang datadump]$ ls

db.dmp dept7.dmp emp30.dmp par.txt ts.dmp

db.log emp30_2.dmp emp.dmp scott_all_tab.dmp ts.log

dept6.dmp emp30_2.log export.log scott.dmp

[oracle@wang datadump]$

或者可以通过参数文件形式执行，则就不需要转义符：

[oracle@wang datadump]$ vi par.txt

userid=scott/tiger

directory=dir_dp

dumpfile=scott_all_tab_01.dmp

schemas=scott

EXCLUDE=TABLE:"IN ('EMP','DEPT')"

~

"par.txt" 5L, 111C written

[oracle@wang datadump]

——执行导出：

[oracle@wang datadump]$ expdp parfile=par.txt

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 10:09:06 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** parfile=par.txt

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows

. . exported "SCOTT"."SL_BASE" 5.914 KB 3 rows

. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/datadump/scott_all_tab_01.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Sep 24 10:10:29 2016 elapsed 0 00:01:21

[oracle@wang datadump]$

——验证：

[oracle@wang datadump]$ ls

db.dmp emp30_2.dmp export.log scott.dmp

db.log emp30_2.log par.txt ts.dmp

dept6.dmp emp30.dmp scott_all_tab_01.dmp ts.log

dept7.dmp emp.dmp scott_all_tab.dmp

?例 2 include操作实例：
示例：expdp \'\/ as sysdba\' directory=AWR_REPORTS_DIR dumpfile=bill_B_FILE_PACKAGE.dmp schemas=bill include=table:\"in$\'B_FILE_PACKAGE\'$\"

任务：出 scott 用户的所有对象，表只导 emp 和 dept 表
[oracle@wang datadump]$ vi par.txt

userid=scott/tiger

directory=dir_dp

dumpfile=scott_all_tab_02.dmp

schemas=scott

INCLUDE=TABLE:"IN ('EMP','DEPT')"

~

"par.txt" 5L, 114C written

——执行导出：

[oracle@wang datadump]$ expdp parfile=par.txt

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 10:19:34 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** parfile=par.txt

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT" 5.929 KB 4 rows

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/datadump/scott_all_tab_02.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Sep 24 10:20:10 2016 elapsed 0 00:00:33

——验证：

[oracle@wang datadump]$ ls

db.dmp dept6.dmp emp30_2.dmp emp30.dmp expdat.dmp par.txt scott_all_tab_02.dmp scott.dmp ts.log

db.log dept7.dmp emp30_2.log emp.dmp export.log scott_all_tab_01.dmp scott_all_tab.dmp ts.dmp

11) Content（导出数据时只请求导出元数据、只请求导出数据，或者请求这两者）
使用 CONTENT 参数在当前操作中可只请求元数据、只请求数据，或者请求这两者。语法：
CONTENT = ALL | METADATA_ONLY | DATA_ONLY

12) SAMPLE
取样率，导出表 t 百分之二十的数据

SCOTT@ORA11GR2>select count(*) from t;

COUNT(*)

----------

14

采样操作：

[oracle@wang datadump]$ expdp scott/tiger directory=dir_dp dumpfile=t.dmp tables=t sample=20

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 10:30:52 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dir_dp dumpfile=t.dmp tables=t sample=20

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 12.79 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."T" 8.117 KB 3 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/datadump/t.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 24 10:31:07 2016 elapsed 0 00:00:12

——查看：

[oracle@wang datadump]$ ls

db.dmp emp30_2.log par.txt t.dmp

db.log emp30.dmp scott_all_tab_01.dmp ts.dmp

dept6.dmp emp.dmp scott_all_tab_02.dmp ts.log

dept7.dmp expdat.dmp scott_all_tab.dmp

emp30_2.dmp export.log scott.dmp

13) FILESIZE
注 filesize 与%U 一起使用%U自动生成递增的序列号，另 filesize 往往与 parallel 一同配合使用。

[oracle@wang datadump]$ expdp system/oracle directory=dir_dp dumpfile=sysytem_%U.dmp schemas=system filesize=1m

Export: Release 11.2.0.4.0 - Production on Sat Sep 24 10:45:02 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dir_dp dumpfile=sysytem_%U.dmp schemas=system filesize=1m

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 320 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/VIEW/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.328 KB 2 rows

. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.882 KB 28 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.835 KB 19 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.484 KB 3 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.289 KB 2 rows

. . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows

. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows

. . exported "SYSTEM"."MVIEW$_ADV_INDEX" 0 KB 0 rows

. . exported "SYSTEM"."MVIEW$_ADV_PARTITION" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows

. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/datadump/sysytem_01.dmp

/home/oracle/datadump/sysytem_02.dmp

/home/oracle/datadump/sysytem_03.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Sep 24 10:45:58 2016 elapsed 0 00:00:56

——验证：

[oracle@wang datadump]$ ll -h sysytem_*

-rw-r----- 1 oracle oinstall 1.0M Sep 24 10:45 sysytem_01.dmp

-rw-r----- 1 oracle oinstall 1.0M Sep 24 10:45 sysytem_02.dmp

-rw-r----- 1 oracle oinstall 312K Sep 24 10:45 sysytem_03.dmp

[oracle@wang datadump]$

14) REMAP_DATAFILE
该选项用于将源数据文件名转变为目标数据文件名，在不同平台之间搬移表空间时可能需要该选项。
REMAP_DATAFIEL=source_datafie:target_datafile

15) REMAP_SCHEMA
该选项用于将源方案的所有对象装载到目标方案中。
REMAP_SCHEMA=source_schema:target_schema

16) REMAP_TABLESPACE
将源表空间的所有对象导入到目标表空间中。
REMAP_TABLESPACE=source_tablespace:target:tablespace

【移动数据】data pump（中） EXPDP 应用