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

1.准备工作
创建目录
[oracle@host01 ~]$ mkdir files

创建目录对象
SYS@PROD1>create or replace directory dir_dp as '/home/oracle/files';

Directory created.

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

Grant succeeded.

2.导出表中指定行（表模式导出）
[oracle@host01 files]$ expdp scott/tiger directory=dir_dp dumpfile=emp30.dmp tables=emp query="'where deptno = 30'";

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 01:52:24 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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/files/emp30.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jan 1 01:52:31 2017 elapsed 0 00:00:06

查看导出生成的文件
[oracle@host01 files]$ ls
emp30.dmp  export.log

3.parfile导出表中指定行(利用parfile参数文件格式导出数据，表模式导出)
[oracle@host01 files]$ vi par.txt
userid=scott/tiger
directory=dir_dp
dumpfile=emp30_2.dmp
logfile=emp30_2.log
tables=emp
query='where deptno = 30'

[oracle@host01 files]$ ls
emp30.dmp  export.log  par.txt

执行参数文件
[oracle@host01 files]$ expdp parfile=par.txt

Export: Release 11.2.0.4.0 - Production on Sat Dec 31 22:38:55 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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/files/emp30_2.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Dec 31 22:39:02 2016 elapsed 0 00:00:06

[oracle@host01 files]$ ls
emp30_2.dmp  emp30_2.log  emp30.dmp  export.log  par.txt

4.导出表（不加query限制条件）
[oracle@host01 files]$ expdp scott/tiger directory=dir_dp dumpfile=emp.dmp tables=emp

Export: Release 11.2.0.4.0 - Production on Sat Dec 31 22:44:12 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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/files/emp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Dec 31 22:44:19 2016 elapsed 0 00:00:06

5.导出 schema（用户模式导出数据）
[oracle@host01 files]$ expdp scott/tiger directory=dir_dp dumpfile=scott.dmp schemas=scott

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 02:32:09 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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"."T"                                 5.023 KB       2 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/files/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jan 1 02:32:30 2017 elapsed 0 00:00:20

6.导出表空间（表空间模式导出）
（注：要使用DBA角色的用户导出，否则只导出本schema的对象）
[oracle@host01 files]$ expdp system/oracle directory=dir_dp dumpfile=ts.dmp logfile=ts.log tablespaces=users
 
Export: Release 11.2.0.4.0 - Production on Sun Jan 1 02:47:09 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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: 1024 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
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 "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"."T"                                 5.023 KB       2 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/files/ts.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sun Jan 1 02:47:29 2017 elapsed 0 00:00:19

[oracle@host01 files]$ ls
export.log  import.log  scott.dmp  ts.dmp  ts.log

7.导出数据库（数据库导出模式）
[oracle@host01 files]$ expdp system/oracle directory=dir_dp dumpfile=db.dmp logfile=db.log full=y

[oracle@host01 files]$ ls
db.dmp  emp30_2.dmp  emp30.dmp  export.log  scott.emp  ts.log
db.log  emp30_2.log  emp.dmp    par.txt     ts.dmp

8.JOB_NAME（即指定导出操作的job_name）
会话1:
[oracle@host01 files]$ 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 Dec 31 23:50:09 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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/files/dept6.dmp
Job "SCOTT"."T_EXPDP_JOB" successfully completed at Sat Dec 31 23:50:16 2016 elapsed 0 00:00:06

会话 2： 在会话 1 运行的同时执行下面的 sql，如果会话 1 执行完毕后，会话 2 的语句则不返回任何信息。
SYS@PROD1>select owner,table_name from dba_tables where table_name='T_EXPDP_JOB';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          T_EXPDP_JOB

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

OWNER_NAME                     JOB_NAME
------------------------------ ------------------------------
JOB_MODE
--------------------------------------------------------------------------------
SCOTT                          T_EXPDP_JOB
TABLE

过一会再查
SYS@PROD1>select owner,table_name from dba_tables where table_name='T_EXPDP_JOB';

no rows selected

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

no rows selected

9.ATTACH（即管理正在进行的导出作业）
[oracle@host01 files]$ 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 Sun Jan 1 00:14:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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/files/dept7.dmp
Job "SCOTT"."T_EXPDP_JOB" successfully completed at Sun Jan 1 00:14:24 2017 elapsed 0 00:00:05

会话 2：当会话2开始执行的时候，那么会话1会暂定
[oracle@host01 files]$ expdp scott/tiger attach=t_expdp_job

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:14:21 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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: 44F79C4A998363A5E0530B0AA8C04E82
 Start Time: Sunday, 01 January, 2017 0:14:18
 Mode: TABLE                          
 Instance: PROD1
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name      Parameter Value:
    CLIENT_COMMAND        scott/******** directory=dir_dp dumpfile=dept7.dmp tables=dept job_name=t_expdp_job
 State: EXECUTING                      
 Bytes Processed: 0
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /home/oracle/files/dept7.dmp
   bytes written: 4,096

Export> status

Job: T_EXPDP_JOB
 Operation: EXPORT                        
 Mode: TABLE                          
 State: COMPLETING                    
 Bytes Processed: 6,073
 Percent Done: 100
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /home/oracle/files/dept7.dmp
   bytes written: 28,672
 
Worker 1 Status:
 Process Name: DW00
 State: WORK WAITING                  

[oracle@host01 files]$ expdp scott/tiger attach=t_expdp_job

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:14:51 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job T_EXPDP_JOB for user SCOTT
ORA-31632: master table "SCOTT.T_EXPDP_JOB" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

10.EXCLUDE/INCLUDE用法

例1.导出scott用户下除了emp和dept表之外的所有表
[oracle@host01 files]$ 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 Sun Jan 1 00:27:44 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  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: 960 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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              261.4 KB    1228 rows
. . exported "SCOTT"."EMP_BAK1"                          8.570 KB      14 rows
. . exported "SCOTT"."FBDB_POINT"                        5.812 KB       1 rows
. . exported "SCOTT"."FBDB_RMAN_SEQ"                     5.812 KB       1 rows
. . exported "SCOTT"."FBDB_SCN"                          5.804 KB       1 rows
. . exported "SCOTT"."FBDB_TIME"                         5.828 KB       2 rows
. . exported "SCOTT"."FB_TABLE"                          5.484 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."T1"                                5.906 KB       3 rows
. . exported "SCOTT"."T3"                                5.859 KB       3 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."T2"                                    0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
 /home/oracle/files/scott_all_tab.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jan 1 00:28:03 2017 elapsed 0 00:00:18

[oracle@host01 files]$ ls
db.dmp  dept6.dmp  emp30_2.dmp  emp30.dmp  export.log  scott_all_tab.dmp  ts.dmp
db.log  dept7.dmp  emp30_2.log  emp.dmp    par.txt     scott.emp          ts.log

或者可以通过参数文件形式执行，则就不需要转义符
[oracle@host01 files]$ vi par.txt
userid=scott/tiger
directory=dir_dp
dumpfile=scott_all_tab_01.dmp
schemas=scott
EXCLUDE=TABLE:"IN ('EMP','DEPT')"

[oracle@host01 files]$ expdp parfile=par.txt

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:30:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** parfile=par.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              261.4 KB    1228 rows
. . exported "SCOTT"."EMP_BAK1"                          8.570 KB      14 rows
. . exported "SCOTT"."FBDB_POINT"                        5.812 KB       1 rows
. . exported "SCOTT"."FBDB_RMAN_SEQ"                     5.812 KB       1 rows
. . exported "SCOTT"."FBDB_SCN"                          5.804 KB       1 rows
. . exported "SCOTT"."FBDB_TIME"                         5.828 KB       2 rows
. . exported "SCOTT"."FB_TABLE"                          5.484 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."T1"                                5.906 KB       3 rows
. . exported "SCOTT"."T3"                                5.859 KB       3 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."T2"                                    0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
 /home/oracle/files/scott_all_tab_01.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jan 1 00:30:32 2017 elapsed 0 00:00:19


例2.导出scott用户的所有对象，表只导emp和dept表
[oracle@host01 files]$ vi par.txt
userid=scott/tiger
directory=dir_dp
dumpfile=scott_all_tab_02.dmp
schemas=scott
INCLUDE=TABLE:"IN ('EMP','DEPT')"

[oracle@host01 files]$ expdp parfile=par.txt

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:46:02 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  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_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
 /home/oracle/files/scott_all_tab_02.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jan 1 00:46:11 2017 elapsed 0 00:00:08

[oracle@host01 files]$ ls
db.dmp  dept6.dmp  emp30_2.dmp  emp30.dmp  export.log  scott_all_tab_01.dmp  scott_all_tab.dmp  ts.dmp
db.log  dept7.dmp  emp30_2.log  emp.dmp    par.txt     scott_all_tab_02.dmp  scott.emp          ts.log

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

12.SAMPLE
取样率，导出表t百分之二十的数据
SYS@PROD1>conn scott/tiger;
Connected.
SCOTT@PROD1>select count(*) from t;
select count(*) from t
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@PROD1>create table t as select * from emp;

Table created.

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

 COUNT(*)
----------
       14

采样操作
[oracle@host01 files]$ expdp scott/tiger directory=dir_dp dumpfile=t.dmp tables=t sample=20

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:53:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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.156 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
 /home/oracle/files/t.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jan 1 00:53:10 2017 elapsed 0 00:00:04

[oracle@host01 files]$ ls
db.dmp     dept7.dmp    emp30.dmp   par.txt               scott_all_tab.dmp  ts.dmp
db.log     emp30_2.dmp  emp.dmp     scott_all_tab_01.dmp  scott.emp          ts.log
dept6.dmp  emp30_2.log  export.log  scott_all_tab_02.dmp  t.dmp

注 filesize 与%U 一起使用%U自动生成递增的序列号，另 filesize 往往与 parallel 一同配合使用。
[oracle@host01 files]$ expdp system/oracle directory=dir_dp dumpfile=sysytem_%U.dmp schemas=system filesize=1m

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 00:56:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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
FLASHBACK automatically enabled to preserve database integrity.
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/files/sysytem_01.dmp
 /home/oracle/files/sysytem_02.dmp
 /home/oracle/files/sysytem_03.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jan 1 00:57:14 2017 elapsed 0 00:00:26

[oracle@host01 files]$ ll -h sys*
-rw-r----- 1 oracle oinstall 1.0M Jan  1 00:57 sysytem_01.dmp
-rw-r----- 1 oracle oinstall 1.0M Jan  1 00:57 sysytem_02.dmp
-rw-r----- 1 oracle oinstall 312K Jan  1 00:57 sysytem_03.dmp

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2131739/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2131739/