【移動資料】data pump（中） EXPDP 應用
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
Copyright (c) 1982, 2013, Oracle. 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
——建立目錄物件，並授權給要使用的使用者：（因為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
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/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
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/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
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/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
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"."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
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: 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
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/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
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/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
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: 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
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_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
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/******** 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
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/******** 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
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.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
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_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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126619/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【移動資料】data pump（下） IMPDP 應用
- expdp impdp Data Pump（資料泵）使用解析
- 【移動資料】data pump（上） 資料泵概述
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- data pump (資料抽取)測試
- 使用隱含Trace引數診斷Oracle Data Pump（expdp）故障Oracle
- Oracle 12c新特性 - Data Pump (expdp/impdp) 功能增強Oracle
- 【DG】怎麼使用Data Pump備份物理備用資料庫資料庫
- 【移動資料】imp的應用
- 【移動資料】exp的應用
- 【Data Pump】Data Pump的並行引數原理並行
- Oracle Data Pump 11G 資料泵元件Oracle元件
- Oracle資料泵(Oracle Data Pump) 19cOracle
- oracle data pumpOracle
- Data Pump 的遠端匯出資料小結
- 三個使用資料泵（Data Pump）的小技巧
- data pump總結
- Exp和資料泵（Data Pump）的query引數使用
- 使用expdp、impdp遷移資料庫資料庫
- Oracle Data Pump 研究(一)Oracle
- 初探data pump export （二）Export
- 初探data pump export（一）Export
- Data Pump with Network importImport
- 從SQLFile檔案分析Oracle Data Pump資料匯入行為SQLOracle
- 10g 資料泵(Data Dump) -- EXPDP & IMPDP [zt]
- 【expdp】10g資料泵expdp工具選項詳解及應用示例
- Oracle GoldenGate 資料同步初始化最佳實戰（Data Pump）OracleGo
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- Data pump學習筆記筆記
- 移動應用中的流設計
- 使用 Oracle Data Pump 解除安裝和載入資料庫內容Oracle資料庫
- 使用impdp,expdp資料泵進入海量資料遷移
- expdp 和network 不落地資料遷移
- 全球移動網際網路應用資料資訊表
- Flowtown:Facebook移動網際網路應用資料
- 文件筆記--Oracle Data Pump 2筆記Oracle
- 文件筆記--Oracle Data Pump 1筆記Oracle
- 有關Data Pump的學習