Data Pump之EXPDP
expdp和impdp的调用方式:
与exp/imp相似,但要注意的是expdp/impdp是服务器端的工具,dump文件最终是保存到目标服务端的,因此在执行expdp/impdp命令前需要为dump文件指定directory对象
SQL> create directory expdp_dir as '/u01/expdp_dir';
Directory created.
SQL> grant read,write on directory expdp_dir to scott;
Grant succeeded.
命令行模式:
导出scott用户下的emp和dept表:
[oracle@localhost ~]$ expdp scott/oracle tables=emp,dept directory=expdp_dir dumpfile=scott_tables.dmp logfile=scott_tables.log
.
.
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"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/scott_tables.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 09:32:22
参数文件调用模式:
该方式适合参数较多的情况下:
[oracle@localhost ~]$ vi /u01/expdp_dir/expdp_parfile.dat
dumpfile=scott_shame.dmp
directory=expdp_dir
logfile=scott_shame.log
[oracle@localhost ~]$ expdp scott/oracle parfile=/u01/expdp_dir/expdp_parfile.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXP_TEST" 560.5 KB 14336 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 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:
/u01/expdp_dir/scott_shame.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:37:47
注意:当调用parfile的时候命令后面或前面再次自定的新的参数,重复参数最后出现的有效
交互模式调用:
expdp的交互模式与exp不大一样,在用户执行导入或导出操作时,通过CTRL+C操作中断操作或者其他原因导致任务中断,此任务并未被取消,而是转向后台,通过再次执行expdp命令,附加attach参数可以连接到中断的任务
[oracle@localhost ~]$ expdp xtt/oracle directory=expdp_dir dumpfile=xtt_schema.dmp l
ogfile=xtt_schema.log
Export: Release 10.2.0.1.0 - Production on Tuesday, 25 March, 2014 10:38:36
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "XTT"."SYS_EXPORT_SCHEMA_01": xtt/******** directory=expdp_dir dumpfile=xtt_schema.dmp logfile=xtt_schema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> help
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started 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.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
Export> status
Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/expdp_dir/xtt_schema.dmp
bytes written: 4,096
Worker 1 Status:
State: EXECUTING
Object Schema: XTT
Object Type: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed Objects: 1
Worker Parallelism: 1
Export> CONTINUE_CLIENT
Total estimation using BLOCKS method: 104.8 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "XTT"."TEST" 64.00 MB 8388608 rows
. . exported "XTT"."EXP_TEST" 560.5 KB 14336 rows
. . exported "XTT"."EMP" 7.257 KB 1 rows
. . exported "XTT"."BONUS" 0 KB 0 rows
. . exported "XTT"."DEPT" 0 KB 0 rows
. . exported "XTT"."SALGRADE" 0 KB 0 rows
Master table "XTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XTT.SYS_EXPORT_SCHEMA_01 is:
/u01/expdp_dir/xtt_schema.dmp
Job "XTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:38:55
操作模式:
整库模式:需要有EXP_FULL_DATABASE和IMP_FULL_DATABASE的权限,在执行备份时需添加FULL=Y参数
[oracle@localhost ~]$ expdp system/oracle full=y directory=expdp_dir dumpfile=ful
l_bak.dmp logfile=full_bak.log
SCHEMA模式模式,导出某一schema下的所有对象,对应的参数是schemas,当然,如果不加任何模式参数,默认的也是导出连接用户schema的所有对象:
[oracle@localhost ~]$ expdp system/oracle schemas=scott directory=expdp_dir dumpfile=scott_bak.dmp logfile=scott_bak.log
.
.
.
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXP_TEST" 560.5 KB 14336 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/expdp_dir/scott_bak.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:07:09
表模式:导入或导出指定的表或表分区
导出scott用户下的emp和dept表:
[oracle@localhost ~]$ expdp system/oracle tables=scott.emp,scott.dept directory=expdp_dir dumpfile=emp_dept.dmp logfile=emp_dept_bak.log
.
.
.
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/emp_dept.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 19:09:59
表空间模式:导出指定的表空间中包含的表,当然,需要注意的是如果导出用户没有该表空间中的所有对象的权限,则只是导出该用户拥有权限的对象,其他对象不会导出。对应的参数是tablespaces:
导出exp_test表空间:
[oracle@localhost ~]$ expdp system/oracle tablespaces=exp_test directory=expdp_dir dumpfile=tps_exp_test.dmp logfile=tps_exp_test_bak.log
.
.
.
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "XTT"."TEST" 64.00 MB 8388608 rows
. . exported "XTT"."EXP_TEST" 560.5 KB 14336 rows
. . exported "XTT"."EMP" 7.257 KB 1 rows
. . exported "XTT"."BONUS" 0 KB 0 rows
. . exported "XTT"."DEPT" 0 KB 0 rows
. . exported "XTT"."SALGRADE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/u01/expdp_dir/tps_exp_test.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 19:16:55
过滤数据或对象:
过滤数据:
query用来使用SQL限制导出的数据,expdp可以对每个表进行单独的限制:
比如导出emp的前2行,和dept的前3行数据:
[oracle@localhost ~]$ expdp scott/oracle directory=expdp_dir dumpfile=scott_tabl
es.dmp nologfile=y tables=emp,dept query=emp:'"where rownum<3"',dept:'"where rownum<4"'
注意以命令行模式调用query参数时,要用’’转译,而在parfile文件中不需要
.
.
.Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.632 KB 3 rows
. . exported "SCOTT"."EMP" 7.335 KB 2 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/scott_tables.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:28:04
parfile模式导出较之要规范简单很多:
[oracle@localhost ~]$ vi /u01/expdp_dir/scott_test_parfile.dat
directory=expdp_dir
dumpfile=scott_tb_test.dmp
nologfile=y
tables=emp,dept
query=emp:"where rownum<3",dept:"where rownum<4"
[oracle@localhost ~]$ expdp scott/oracle parfile=/u01/expdp_dir/scott_test_parfile.dat
.
.
.
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.632 KB 3 rows
. . exported "SCOTT"."EMP" 7.335 KB 2 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/scott_tb_test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:35:52
SAMPLE参数指定导出数据的百分比,该参数的值可以设置在0.000001到0.999999之间:
导出empt表中50%的数据:
SQL> select count(*) from emp;
COUNT(*)
----------
14
[oracle@localhost ~]$ expdp scott/oracle directory=expdp_dir dumpfile=emp_0.5.dmp nologfile=y tables=emp sample=emp:50
.
.
.
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" 7.570 KB 8 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/emp_0.5.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:48:10
如果多次执行导出会发现每次导出的数据比例并不是指定的百分比,只能是接近指定的值
过滤对象:
exclude指定在导入或导出时跳过的对象,需要注意的是如果该参数指定的对象有依赖对象,那么所依赖的对象也将跳过。
导出emp表时不再导出该表的索引:
[oracle@localhost ~]$ expdp scott/oracle directory=expdp_dir dumpfile=mp_no_index.dmp
nologfile=y tables=emp exclude=index
.
.
.
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/expdp_dir/mp_no_index.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:52:56
当然也可以指定单个的索引名称,该参数也可以使用通配符,定义符等;exclude的语法如下:
exclude=object_type[:name_clause] [,...]
比如指定名为EMPNO_INDEX的索引:
exclude=index:”EMPNO_INDEX”
include指定包含的导入导出对象,语法格式与exclude完全一致,但要注意的是该参数不能与exclude同时出现。
利用include参数导出scott用户下emp和dept表
[oracle@localhost ~]$ expdp system/oracle directory=expdp_dir dumpfile=emp_dept_inclu
de.dmp nologfile=y schemas=scott include=table:\"in \(\'EMP\',\'DEPT\'\)\";
.
.
dumpfile=emp_dept_include.dmp nologfile=y schemas=scott include=table:"in ('EMP','DEPT')"
.
.
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/expdp_dir/emp_dept_include.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:14:23
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1130042/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-1130042/