expdp和impdp的用法.docx
《expdp和impdp的用法.docx》由会员分享,可在线阅读,更多相关《expdp和impdp的用法.docx(25页珍藏版)》请在冰豆网上搜索。
expdp和impdp的用法
Expdp和Impdp的用法
马飞
2016-7-18
一、概述
Oracle官方对此的形容是:
OracleDataPumptechnologyenablesVeryHigh-Speedmovementofdataandmetadatafromonedatabasetoanother.其中VeryHigh-Speed是亮点。
先说数据泵提供的主要特性(包括,但不限于):
1.支持并行处理导入、导出任务
2.支持暂停和重启动导入、导出任务
3.支持通过DatabaseLink的方式导出或导入远端数据库中的对象
4.支持在导入时通过Remap_schema、Remap_datafile、Remap_tablespace几个参数实现导入过程中自动修改对象属主、数据文件或数据所在表空间。
5.导入/导出时提供了非常细粒度的对象控制。
通过Include、Exclude两个参数,甚至可以详细制定是否包含或不包含某个对象。
二、什么是Directory对象
Directory对象是Oracle10g版本提供的一个新功能。
他是一个指向,指向了操作系统中的一个路径。
每个Directory都包含Read,Write两个权限,可以通过Grant命令授权给指定的用户或角色。
拥有读写权限的用户就可以读写该Directory对象指定的操作系统路径下的文件。
无论在什么地方使用expdp,生成的文件最终页是在服务器上(Directory指定的位置)
三、如何调用
1.命令行方式
最简单的调用,但是写的参数有限,建议使用参数文件的方式。
2.参数文件方式
最常用的方式。
通常需要先编写一个参数文件。
指定导出时需要的各种参数。
然后以如下方式调用。
expdpuser/pwdparfile=xxx.par
这个xxx.par即是我们编辑的参数文件。
注意,在这个命令行后面,同样可以再跟别的参数,甚至是在par参数文件中指定过的参数。
如果执行命令中附加的参数与参数文件中的参数有重复,最终采用哪个参数,会以参数最后出现的位置而定。
如:
expdpuser/pwdparfile=xxx.parlogfile=a.log,如果在参数文件中也指定了logfile,这里会以命令行中的logfile为准;如:
expdpuser/pwdlogfile=a.logparfile=xxx.par,而这个,则会以参数文件中的为准,因为parfile=xxx.par写在命令行的后面。
3.交互方式
DataPump导入导出任务支持停止,重启等状态操作。
如用户执行导入或者导出任务,执行了一半时,使用Crtl+C中断了任务(或其他原因导致的中断),此时任务并不是被取消,而是被转移到后台。
可以再次使用expdp/impdp命令,附加attach参数的方式重新连接到中断的任务中,并选择后续的操作。
这就是交互方式。
Warning:
什么是attach参数,每执行一个导入,或者导出,在命令的第一行,会有以下信息:
Starting“BAM”.”SYS_EXPORT_SCHEMA_01″:
bam/********parfile=expdp_tbs.par,这个SYS_EXPORT_SCHEMA_01就是我们的attach参数。
-bash-3.00$expdpbam/bamparfile=expdp_tbs.par
Export:
Release10.2.0.4.0–64bitProductiononFriday,13August,201016:
35:
18
Copyright(c)2003,2007,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0–64bitProduction
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
Starting“BAM”.”SYS_EXPORT_SCHEMA_01″:
bam/********parfile=expdp_tbs.par
如果想使用交互方式,可以使用如:
expdpattachSYS_EXPORT_SCHEMA_01进入到交互模式
SQL>createtablespaceappsdatafile'/opt/app/oradata/orcl/apps01.dbf'size200m;
Tablespacecreated.
SQL>createuserappsidentifiedbyappsdefaulttablespaceapps;
Usercreated.
SQL>grantconnect,dba,resource,allprivilegestoapps;
Grantsucceeded.
SQL>connapps/apps
Connected.
SQL>createtablelx1asselect*fromdba_objects;
Tablecreated.
SQL>createtablelx2asselect*fromdba_segments;
Tablecreated.
SQL>createindexind_lx1_n1onlx1(owner,object_name)nologgingtablespaceapps;
Indexcreated.
四、操作模式
1.全库模式
导入或者导出整个数据库,对应impdp/expdp命令中的full参数,只有拥有dba或者exp_full_database和imp_full_database权限的用户才能执行。
expdpsystem/Jusfoun*DIRECTORY=dumpDUMPFILE=full.dmpfull=y
2.Schema模式
导出或导入Schema下的自有对象,对应impdp/expdp命令中的Schemas参数,这是默认的操作模式。
如果拥有dba或者exp_full_database和imp_full_database权限的用户执行的话,就可以导出或导入多个Schema中的对象。
expdpapps/appsDIRECTORY=dumpDUMPFILE=apps_schema.dmplog=apps_schema.logschemas=apps
3.表模式
导出指定的表或者表分区(如果有分区的话)以及依赖该表的对象(如该表的索引,约束等,不过前提是这些对象在同一个Schema中,或者执行的用户有相应的权限)。
对应impdp/expdp命令中的Table参数。
expdpapps/appsDIRECTORY=dumpDUMPFILE=apps_tab2.dmplog=apps_tab2.logtables=lx1,lx2
4.表空间模式
导出指定的表空间中的内容。
对应impdp/expdp中的Tablespaces参数,这种模式类似于表模式和Schema模式的补充。
expdpapps/appsDIRECTORY=dumpDUMPFILE=apps_tablespace.dmplog=apps_tablespace.logtablespaces=apps
5.传输表空间模式
对应impdp/expdp中的Transport_tablespaces参数。
这种模式与前面几种模式最显著的区别是生成的Dump文件中并不包含具体的逻辑数据,而只导出相关对象的元数据(即对象的定义,可以理解成表的创建语句),逻辑数据仍然在表空间的数据文件中,导出时需要将元数据和数据文件同时复制到目标端服务器。
这种导出方式效率很高,时间开销主要是花在复制数据文件产生的I/O上。
expdp执行传输表空间模式的导出,用户必须拥有exp_full_database角色或者DBA角色。
而通过传输表空间模式导入时,用户必须拥有imp_full_database角色或者DBA角色。
altertablespaceappsreadonly;
expdpsystem/Jusfoun*DIRECTORY=dumpDUMPFILE=apps_tran_tablespace.dmplog=apps_tran_tablespace.logtransport_tablespaces=appstransport_full_check=y
createorreplacedirectorydumpas'/home/grid/dump';
Directorycreated.
grantread,writeondirectorydumptosys,system;
Grantsucceeded.
impdpsystem/Jusfoun*dumpfile=apps_tran_tablespace.dmpdirectory=dumptransport_datafiles=apps01.dbfremap_schema=apps:
apps1
selectsegment_name,segment_type,owner,tablespace_name
fromdba_segmentswheretablespace_namein('USERS','APPS');
五、过滤数据
过滤数据主要依赖于Query和Sample两个参数。
其中Sample参数主要针对expdp导出功能。
5.1Query
与exp命令中的Query功能类似,不过Expdp中,该参数功能得到了增强,控制的粒度更细。
Expdp中的Query也是指定类似where语句来限定记录。
语法如下:
Query=[Schema.][Table_name:
]Query_clause
默认情况如果不指定Schema.table_name,则Query_clause针对所有导出的表有效,或者你可以为每一个表指定不同的Query_clause,如:
导出a表中所有id<5的记录,导出b表中所有name=’a’的记录,则Query的参数应该如下:
Query=A:
”Whereid<5″,B:
”Wherename=’a’”
如果Where条件前没有指定Schema名或者表名的话,默认就是针对当前所有要被导出的表。
如:
Query=Whereid<5
Warning:
建议把Query参数放入到参数文件中使用,以避免转义符带来的麻烦。
morequery.par
directory=dump
dumpfile=apps_tab_query.dmp
#schemas=apps
tables=lx1
query=lx1:
"whereobject_type='TABLE'"
reuse_dumpfiles=y
#query=lx1:
"whererownum<100"
expdpapps/appsparfile=query.par
注意:
tables参数与schemas参数不能同时出现在expdp命令中。
5.2.Sample
该参数用来指定导出数据的百分比,可指定的值的范围从0.000001到99.999999,语法如下:
Sample=[[Schema_name.]Table_name:
]sample_percent
指定该参数以后,EXPDP导出将自动控制导出的记录量,如导出A表中50%的记录,设置的Sample参数如下:
Sample=A:
50
Warning:
Sample_percent指定的值只是一个参考值,EXPDP会根据数据量算出一个近似值。
六、过滤对象
过滤对象主要依赖于Include和Exclude两个参数。
这两个参数作用正好相反,在这两个参数中,可以指定你知道的任何对象类型(如:
Package、Procedure、Table等等)或者对象名称(支持通配符)
1.Exclude反规则
指定不被包含的对象类型或者对象名称。
指定了该参数以后,指定的对象类型对应的所有对象都不会被导入或导出。
如果被排除的对象有依赖的对象,那么其依赖的对象也不会被导入或导出。
如:
通过Exclude参数指定不导出表对象的话,不仅指定的表不会被导出,连这些表关联的Index、Check等都不会被导出。
语法如下:
Exclude=object_type[:
name_clause][,...]
Warning:
Exclude参数支持同时指定多个参数值,如:
不导入A表的索引,也不导入B表的约束(假设A表的索引以idx_a开头,B表的约束以chk_b开头),连所有的授权都不想导入,那么Exclude参数设置如下:
Exclude=Index:
”like‘idx_a%’”,Constraint:
”like‘chk_b%’”,Grant
Warning:
建议把Exclude参数放入到参数文件中使用,以避免转义符带来的麻烦。
2.Include正规则
与Exclude正好相反。
指定包含的对象类型或者对象名称。
Warning:
由于两个参数功能正好相反,因此在执行导入或导出命令时,两个参数不能同时使用,否则Oracle也不知道你想要干什么啦。
七、高级过滤
在导出/导入的时候,我们常常有这样的需求,只想导出/导入表结构,或者只想导出/导入数据。
幸运的是数据泵也提供了该功能。
使用Content参数。
该参数有三个属性
1)ALL:
导出/导入对象定义和数据,该参数的默认值就是ALL
2)DATA_ONLY:
只导出/导入数据。
3)METADATA_ONLY:
只导出/导入对象定义。
Warning:
有一点值得注意的时,在执行导出的时候,如果使用了高级过滤,如只导出了数据,那么导入时,需要确保数据定义已经存在。
否则数据都变成没有主子了。
如果数据定义已经存在,导入时最好指定data_only,否则会触发ORA-39151错误,因为对象已经存在了。
过滤已经存在的数据
我们知道,导入的表对象在目标库中已经存在,并且目标端没有创建数据完整性约束条件(RI)来检验数据的话,就有可能造成数据被重复导入。
数据泵提供了一个新的参数Table_exists_action,可以一定程度上降低重复数据的产生。
该参数用来控制如果要导入的表对象存在,执行什么操作。
有以下几个参数值:
1)SKIP:
跳过该表,继续处理下一个对象。
该参数默认就是SKIP。
值得注意的是,如果你同时指定了CONTENT参数为Data_only的话,SKIP参数无效,默认为APPEND。
2)APPEND:
向现有的表中添加数据。
3)TRUNCATE:
TRUNCATE当前表,然后再添加记录。
使用这个参数需要谨慎,除非确认当前表中的数据确实无用。
否则可能造成数据丢失。
4)REPLACE:
删除并重建表对象,然后再向其中添加数据。
值得注意的是,如果同时指定了CONTENT参数为Data_only的话,REPLACE参数无效。
八、重定义表的Schema或表空间
我们还可能会遇到这样的需求,把A用户的对象转移到B用户,或者更换数据的表空间。
数据泵通过Remap_Schema和Remap_tablespace参数实现了该功能。
1)REMAP_SCHEMA:
重定义对象所属Schema
该参数的作用类似IMP中的Fromuser+Touser,支持多个Schema的转换,语法如下:
REMAP_SCHEMA=Source_schema:
Target_schema[,Source_schema:
Target_schema]
如把A的对象转换到C用户,将C转换到D用户。
Remap_schema=a:
b,c:
d
Warning:
不能在同一个IMPDP命令中指定remap_schema=a:
b,a:
c.
2)REMAP_TABLESPACE:
重定义对象所在的表空间。
该参数用来重映射导入对象存储的表空间,支持同时对多个表空间进行转换,相互间用逗号分割。
语法如下:
REMAP_TABLESPACE=Source_tablespace:
Target_tablespace[,Source_tablespace:
Target_tablespace]
Warning:
如果使用Remap_tablespace参数,则要保证导入的用户对目标表空间有读写权限。
九、优化导入/导出效率
对于大数据量来说,我们不得不考虑效率问题。
数据泵对效率也提出了更高的要求。
甚至官方的描述就是OracleDataPumptechnologyenablesVeryHigh-Speedmovementofdataandmetadatafromonedatabasetoanother.这里的VeryHigh-Speed依赖我们的parallel参数。
所有的优化操作都会有三种结果:
变得更好、没有变化、变得更差。
Parallel参数也是这样,并不是指定一个大于1的参数,性能就会有提升。
1)对于导出的parallel
对于导出来说,由于dump文件只能由一个线程进行操作(包括I/O处理),因此如果输出的DUMP文件只有一个,即使你指定再多的并行,实际工作仍然是一个,而且还会触发ORA-39095错误。
因此,建议设置该参数小于或等于生成的DUMP文件数量。
那么,如何控制生成的DUMP文件数量呢?
EXPDP命令提供了一个FILESIZE参数,用来指定单个DUMP文件的最大容量,要有效的利用parallel参数,filesize参数必不可少。
举例:
某用户对象占用了4G左右的空间,实际导出后的DUMP文件约为3G,我们尝试在导出该用户时指定并行度为4,设置单个文件不超过500M,则语法如下:
grantread,writeondirectorydumptosys,system;
$expdpuser/pwddirectory=dump_filedumpfile=expdp_20100820_%U.dmplogfile=expdp_20100820.logfilesize=500Mparallel=4
2)对于导入的parallel
对于导入来说,使用parallel参数则要简单的多,我认为导入更能体现parallel参数的优势。
参数设置为几,则认为同时将几张表的内容导入到库中。
举例:
某dmp文件中包含了200张表,我们尝试在导入该DMP文件时指定并行度为10,则语法如下:
$impdpuser/pwddirectory=dump_filedumpfile=expdp_20100820.dmplogfile=impdp_20100820.logparallel=10
十、执行导出
1.如何起步
前面介绍了一些基本知识,现在我们来试试如何操作吧。
举例如下:
从10.1.133.98服务器上,将除数据之外的所有信息导入到10.1.133.88数据库中。
1)创建一个Directory对象,并授予用户读写权限。
SQL>createdirectorydump_fileas‘/home/oracle/backup’;
Directorycreated.
SQL>grantread,writeondirectorydump_filetobam;
Grantsucceeded.
如上,生成的DMP文件将会放在/home/oracle/backup目录下。
2)编写一个导出的参数文件。
-bash-3.00$viexpdp_tbs.par
DIRECTORY=DUMP_FILE
DUMPFILE=expdp_bamdb2bamtest.dmp
LOGFILE=expdp_bamdb2bamtest.log
CONTENT=METADATA_ONLY
如上,指定了生成的dmp文件名以及日志名(这些都会被放在/home/oracle/backup下),以及导出模式为:
metadata_only–只导出结构,不包含数据。
3)执行导出命令
-bash-3.00$expdpbam/bamparfile=expdp_tbs.par
Export:
Release10.2.0.4.0–64bitProductiononFriday,13August,201016:
35:
18
Copyright(c)2003,2007,Oracle.Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0–64bitProduction
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
Starting“BAM”.”SYS_EXPORT_SCHEMA_01″:
bam/********parfile=expdp_tbs.par
ProcessingobjecttypeSCHEMA_EXPORT/USER
ProcessingobjecttypeSCHEMA_EXPORT/SYSTEM_GRANT
ProcessingobjecttypeSCHEMA_EXPORT/ROLE_GRANT
ProcessingobjecttypeSCHEMA_EXPORT/DEFAULT_ROLE
ProcessingobjecttypeSCHEMA_EXPORT/TABLESPACE_QUOTA
ProcessingobjecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ProcessingobjecttypeSCHEMA_EXPORT/TYPE/TYPE_SPEC
ProcessingobjecttypeSCHEMA_EXPORT/SEQUENCE/SEQUENCE
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEX
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/COMMENT
ProcessingobjecttypeSCHEMA_EXPORT/FUNCTION/FUNCTION
ProcessingobjecttypeS