数据泵 EXPDP 导出工具的使用
数据泵EXPDP导出工具的使用--=================================
对于Oracle数据库之间的导入导出,可以使用Oracle提供的导入导出工具EXP/IMP来实现。EXP/IMP是Oracle早期提供的数据导入导出工具。在Oracle 10g中,提供了高速导入导出数据泵IMPDP,EXPDP,本文主要讲述EXPDP的用法。
关于IMPDP的用法,请参照:数据泵IMPDP导入工具的使用
一、数据泵的体系结构
数据泵是一个基于服务器端的高速导入导出工具,通过dbms_datapump包来调用
提供expdp,impdp,以及基于Web页面来实现导入导出
提供两种数据数据方式方式:直接路径、外部表
可以定制数据泵作业,以及从作业中分离和重新附加到作业
服务器端的数据泵是直接访问数据文件与SGA,不必通过会话进行访问
数据泵进程
对Unix系统而言,数据泵进程为expdp,impdp
对Windows系统而言,数据泵进程为expdp.exe,impdp.exe
启动一个DataPump作业,至少会启动下列两个进程,一个Data Pump Master(DMnn),一个或多个工作进程(DWnn),主进程控制工作进程
如果多个DataPump作业同时运行,那么每个作业都具有自己的DMnn进程以及自己的DWnn进程
如果设置了并行技术,则每个DWnn进程可以使用两个或多个并行执行服务器(名称为Pnnn)
DataPump生成下列三种形式的文件
SQL文件:描述指定作业所包含对象的若干DDL语句
转储文件:即包含数据和元数据的文件
日志文件:用于记录导出时的相关信息
目录
用于设置导入导出文件所在或存放的位置 createdirectory dump_scottas/home/oracle/dump/scott';
可以通过dba_directories来查看系统中已创建的目录select*fromdba_directories;
对于创建的目录必须授予用户读写目录的权限 grantread,writeondirectory dump_scotttoscott;
二、数据泵的优点
在Oracel 10g中提供的数据泵,较之i时代的导入导出工具(imp,exp),除了能实现imp/exp的功能之外,提供了更好的性能,下面是数据泵的优点
为数据及数据对象提供更细微级别的选择性(使用exclude,include,content参数)
可以设定数据库版本号(主要是用于兼容老版本的数据库系统)
并行执行
预估导出作业所需要的磁盘空间(使用estimate_only参数)
支持分布式环境中通过数据库链接实现导入导出
支持导入时重新映射功能(即将对象导入到新的目标数据文件,架构,表空间等)
支持元数据压缩及数据采样
三、数据泵程序接口及模式
数据泵导入导出接口如下
命令行接口
参数文件
交互式命令行接口
数据库控制台
数据泵导入导出模式
整个数据库
架构
表
表空间
传输表空间
四、导出工具expdp
1.它是操作系统下一个可执行的文件存放目录/ORACLE_HOME/bin
[oracle@oradb bin]$ ls-lh expdp
-rwxr-x--x 1 oracle oinstall 174K Sep 13 20:01 expdp
expdp导出工具将数据库中数据备份压缩成一个二进制系统文件.可以在不同OS间迁移
expdb支持三种模式:
a.表模式: 导出用户所有表或者指定的表
b.用户模式:导出用户所有对象以及对象中的数据
c.导出表空间:导出数据库中特定的表空间
d.整个数据库: 导出数据库中所有对象
使用expdp-?可以查看expdp命令的用法并启动交互进程,也可使用expdp-help来查看更详细的帮助信息
[oracle@oradb bin]$ expdp-?
abort_step Undocumented feature
access_method Data Access Method-defaultisAutomatic
attach Attachtoexisting job-nodefault)''
compression Contenttoexport:defaultisMETADATA_ONLY
content Contenttoexport:defaultisALL
directory Defaultdirectory specification
dumpfile dumpfile names:formatis(file1,...)defaultisexpdat.dmp
encryption_password Encryptionkeytobe used
estimate Calculatesizeestimate:defaultisBLOCKS
estimate_only Only estimate the lengthofthe job:defaultisN
exclude Export excludeoption:nodefault
filesize filesize:thesizeofexportdumpfiles
flashback_time databasetimetobe usedforflashback export:nodefault
flashback_scn system change numbertobe usedforflashback export:nodefault
full indicates afullmode export
include exportincludeoption:nodefault
ip_address IP AddressforPLSQL debugger
help help:display descriptiononexport parameters,defaultisN
job_name JobName:nodefault)''
keep_master keep_master:Retain jobtableupon completion
log_entry logentry
logfile logexport messagestospecifiedfile
metrics Enable/disableobject metrics reporting
mp_enable Enable/disablemulti-processingforcurrentsession
network_link Network mode export
nologfile Noexportlogfilecreated
package_load Specify howtoloadPL/SQL objects
parallel DegreeofParallelism:defaultis1
parallel_threshold DegreeofDML Parallelism
parfile parameterfile:nameoffilethatcontainsparameter specifications
query query usedtoselecta subsetofrowsforatable
sample Specify percentageofdatatobe sampled
schemas schemastoexport:formatis'(schema1, .., schemaN)'
silent silent:display information,defaultisNONE
status Intervalbetweenstatus updates
tables Tablestoexport:formatis'(table1, table2, ..., tableN)'
tablespaces tablespacestotransport/recover:formatis'(ts1,..., tsN)'
trace Traceoption:enablesql_traceandtimed_stat,defaultis0
transport_full_check TTS perform. testforobjectsinrecoveryset:defaultisN
transport_tablespaces Transportable tablespaceoption:defaultisN
tts_closure_check Enable/disabletransportable containmentcheck:defisY
userid user/passwordtoconnecttooracle:nodefault
version Job version:Compatibleisthedefault
Export:Release 10.2.0.1.0-ProductiononMonday,20 September,2010 14:22:56
Copyright(c)2003,2005,Oracle. Allrights reserved.
Username:
2.导出工具expdp非交互式命令行方式的例子
a.基于表模式的导出
SQL>createdirectory dump_scottas'/home/oracle/dump/scott';
Directory created.
SQL>select*fromdba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS DUMP_SCOTT /home/oracle/dump/scott
SQL>grantread,writeondirectory dump_scotttoscott;
Grantsucceeded.
SQL>!
[oracle@oradb /]$ mkdir/home/oracle/dump
[oracle@oradb /]$ mkdir/home/oracle/dump/scott
[oracle@oradb ~]$ expdp scott/tiger directory=dump_scott dumpfile=dumptab.dmp /
>logfile=scott.logtables=dept,emp
Export:Release 10.2.0.1.0-ProductiononMonday,20 September,2010 14:55:23
Copyright(c)2003,2005,Oracle. Allrights reserved.
Connectedto:OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Production
Withthe Partitioning,OLAPandData Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********/directory=dump_scott dumpfile=dumptab.dmp logfile=scott.logtables=dept,emp
Estimateinprogress using BLOCKS method...
Processing objecttypeTABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method:128 KB
Processing objecttypeTABLE_EXPORT/TABLE/TABLE
Processing objecttypeTABLE_EXPORT/TABLE/INDEX/INDEX
Processing objecttypeTABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing objecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing objecttypeTABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing objecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
..exported "SCOTT"."DEPT" 5.656 KB 4 rows
..exported "SCOTT"."EMP" 7.820 KB 14 rows
Mastertable"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
DumpfilesetforSCOTT.SYS_EXPORT_TABLE_01is:
/home/oracle/dump/scott/dumptab.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:55:56
--后台中DMnn,DWnn进程为启动DataPump是产生的进程
[oracle@oradb /]$ ps-ef|grep ora_d
oracle 3445 1 0 14:19 ? 00:00:00 ora_dbw0_orcl
oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl
[oracle@oradb ~]$ ls-lh/home/oracle/dump/scott
total 132K
-rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp
-rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log
b.基于用户模式导出
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=dumpscott.dmp schemas=scott
Export:Release 10.2.0.1.0-ProductiononMonday,20 September,2010 15:08:55
Copyright(c)2003,2005,Oracle. Allrights reserved.
Connectedto:OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Production
Withthe Partitioning,OLAPandData Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********/directory=dump_scott dumpfile=dumpscott.dmp schemas=scott
Estimateinprogress using BLOCKS method...
Processing objecttypeSCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method:192 KB
Processing objecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing objecttypeSCHEMA_EXPORT/TABLE/TABLE
Processing objecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing objecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing objecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing objecttypeSCHEMA_EXPORT/TABLE/COMMENT
Processing objecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing objecttypeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
..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
Mastertable"SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
DumpfilesetforSCOTT.SYS_EXPORT_SCHEMA_01is:
/home/oracle/dump/scott/dumpscott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:23
c.基于表空间导出
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=users1.dmp,user2.dmp /
>compression tablespaces=users
Export:Release 10.2.0.1.0-ProductiononMonday,20 September,2010 15:17:35
Copyright(c)2003,2005,Oracle. Allrights reserved.
Connectedto:OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Production
Withthe Partitioning,OLAPandData Mining options
Starting "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/********/directory=dump_scott dumpfile=users1.dmp,user2.dmp compression tablespaces=users
Estimateinprogress using BLOCKS method...
Processing objecttypeTABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method:192 KB
Processing objecttypeTABLE_EXPORT/TABLE/TABLE
Processing objecttypeTABLE_EXPORT/TABLE/INDEX/INDEX
Processing objecttypeTABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing objecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing objecttypeTABLE_EXPORT/TABLE/COMMENT
Processing objecttypeTABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing objecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
..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
Mastertable"SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
DumpfilesetforSCOTT.SYS_EXPORT_TABLESPACE_01is:
/home/oracle/dump/scott/users1.dmp
Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 15:17:51
[oracle@oradb /]$ ls-lh/home/oracle/dump/scott
total 524K
-rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 compression.dmp
-rw-r----- 1 oracle oinstall 224K Sep 20 15:09 dumpscott.dmp
-rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp
-rw-r--r-- 1 oracle oinstall 1.6K Sep 20 15:17 export.log
-rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log
-rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 user2.dmp
-rw-r----- 1 oracle oinstall 148K Sep 20 15:17 users1.dmp
d.导出整个数据库,且使用并行导出方式
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=4full=y
Export:Release 10.2.0.1.0-ProductiononMonday,20 September,2010 15:24:02
Copyright(c)2003,2005,Oracle. Allrights reserved.
Connectedto:OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Production
Withthe Partitioning,OLAPandData Mining options
ORA-31631:privileges are required
ORA-39161:Fulldatabasejobs require privileges
[oracle@oradb /]$ sqlplus/nolog
SQL*Plus:Release 10.2.0.1.0-ProductiononMon Sep 20 15:24:16 2010
Copyright(c)1982,2005,Oracle. Allrights reserved.
SQL>conn/assysdba
Connected.
Grantsucceeded.
SQL>!
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=6full=y
--中间过程省略
[oracle@oradb dump]$ ls-lh./scott/fu*
-rw-r----- 1 oracle oinstall 19M Sep 20 15:36 ./scott/full20_01.dmp
-rw-r----- 1 oracle oinstall 22M Sep 20 15:34 ./scott/full20_02.dmp
-rw-r----- 1 oracle oinstall 18M Sep 20 15:36 ./scott/full20_03.dmp
-rw-r----- 1 oracle oinstall 15M Sep 20 15:36 ./scott/full20_04.dmp
-rw-r----- 1 oracle oinstall 5.4M Sep 20 15:36 ./scott/full20_05.dmp
-rw-r----- 1 oracle oinstall 196K Sep 20 15:33 ./scott/full20_06.dmp
[oracle@oradb dump]$ ps-ef|grep ora_d
oracle 3445 1 0 14:19 ? 00:00:01 ora_dbw0_orcl
oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl
oracle 23443 1 4 15:32 ? 00:00:01 ora_dm00_orcl
oracle 23494 1 23 15:32 ? 00:00:08 ora_dw01_orcl
oracle 23673 1 11 15:33 ? 00:00:02 ora_dw02_orcl
oracle 23675 1 16 15:33 ? 00:00:03 ora_dw03_orcl
oracle 23677 1 8 15:33 ? 00:00:01 ora_dw04_orcl
oracle 23679 1 5 15:33 ? 00:00:00 ora_dw05_orcl
oracle 23681 1 2 15:33 ? 00:00:00 ora_dw06_orcl
oracle 23696 2416 0 15:33 pts/1 00:00:00 grep ora_d
五、数据泵的监控
1.查询dba_directories获得所创建的目录
2.可以查询dba_datapump_jobs来查看数据泵作业的运行情况,也可以利用ATTACH重新连接上还在进行的JOB
每个datapump可以通过job_name参数来指定作业名称,如未指定,则系统使用默认的作业名称,如下面的视图中为SYS_EXPORT_FULL_01
通过v$session_longops也可以查看长时间运行的datapump job的具体内容
SQL>selectowner_name owr,job_name jbn,operation ope,job_mode jbm,state,degree,
2 attached_sessions atts,datapump_sessions dats
3 fromdba_datapump_jobs;
OWR JBN OPE JBM STATE DEGREE ATTS DATS
---------- -------------------- -------------------- --------------- ---------- ---------- ---------- ----------
SCOTT SYS_EXPORT_FULL_01 EXPORT FULL COMPLETING 2 1 2
SQL>selectsid,serial#,session_type
2 from v$session s,dba_datapump_sessions d
3 wheres.saddr=d.saddr;
SID SERIAL# SESSION_TYPE
---------- ---------- --------------
143 10 DBMS_DATAPUMP
149 37 MASTER
132 3 WORKER
136 3 WORKER
135 4 WORKER
141 5 WORKER
128 2 WORKER
142 4 WORKER
3.监控数据泵的逻辑备份程度
SELECTsid,serial#,context,sofar,totalwork,
ROUND(sofar/totalwork*100,2)"%_COMPLETE"
FROMv$session_longops
WHEREopnameLIKE'%EXP%'
ANDtotalwork!=0
ANDsofar<>totalwork;
SIDSERIAL#CONTEXTSOFAR TOTALWORK %_COMPLETE
13033 0 54 70 77.14
六、expdp的常用参数
1.content:该选项用于指定要导出的内容.默认值为ALL
CONTENT={ALL|DATA_ONLY|METADATA_ONLY}
expdp scott/tiger schemas=scottcontent=all
expdp scott/tiger tables=empcontent=data_only directory=dump_scott dumpfile=empdata.dmp(只导出对象数据)
expdp scott/tiger tables=empcontent=metadata_only directory=dump_scott dumpfile=empmd.dmp(只有定义信息)
2.estimate:指定估算被导出表所占用磁盘空间分方法.默认值是blocks
expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=statistics
expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=blocks
3.extimate_only:指定是否只估算导出作业所占用的磁盘空间,默认值为N
expdp scott/tiger schemas=scott estimate_only=y
设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,
为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作.
4.exclude:该选项用于指定执行操作时释放要排除对象类型或相关对象
exclude=view
exclude=package
exclude=index:"like 'EMP%'
object_type用于指定要排除的对象类型,name_expr用于指定要排除的具体对象.exclude和include不能同时使用
expdp scott/tiger schemas=scott exclude=view dumpfile=a9.dmp
include = object_type[:"name_expr"]
5.filesize:指定导出文件的最大尺寸,默认为,(表示文件尺寸没有限制)
6.flashback_scn:前提闪回功能开启
expdp scott/tiger tables=emp dumpfile=e2.dmp flashback_scn=4284715
如果闪回的时间点该对象结构发生变化,将报错(比如该对象没有创建或者ddl操作)
7.flashback_time:指定导出特定时间点的表数据
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME="TO_TIMESTAMP(’-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)"
windows下:
C:/>expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=a.dmp
flashback_time=/"TO_TIMESTAMP('06-04-2010 11:24:26','DD-MM-YYYY HH24:MI:SS')/"
8.query导出查询得到的结果集
query=scott.emp :"wheredeptno=30andsal>3500"
9.sample使用该参数进行对导出的数据进行采样
sample="scott"."emp":20
expdp scott/tiger directory=dump_scott dumpfile=sam.dmp sample=30
10.dumpfile指定导出时的文件名
dumpfile=scott_tb.dmp
dumpfile=scott_tb_%u.dmp %u用于扩展导出的文件名,固定长度为个字符,从开始递增,使用并行导出时建议指定该参数