oracle expdp/impdp的常用用法总结
[toc]
oracle expdp/impdp的常用用法总结
1、导出某个用户下的所有东东
schemas=schemas 名称 liups
1 | expdp system/liups.com schemas=liups DIRECTORY=DATA\_PUMP\_DIR dumpfile=liups.com.dmp logfile=liups.com.log |
2、只导出表结构不导出数据、或者只导出数据不导出表结构
CONTENT 指定要卸载的数据。 有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY 。 DATA_ONLY ：只导出数据 METADATA_ONLY ： 只导出表结构(元数据) 只导出表结构不导出数据
1 | expdp system/liups.com schemas=liups DIRECTORY=DATA\_PUMP\_DIR CONTENT=METADATA\_ONLY dumpfile=liups.com.dmp logfile=liups.com.log |
只导出数据不导出表结构 expdp system/liups.com schemas=liups DIRECTORY=DATA_PUMP_DIR CONTENT=DATA_ONLY dumpfile=liups.com.dmp logfile=liups.com.log 3、从一个用户导入到另一个用户 REMAP_SCHEMA=元用户名称:新导入的用户名称(可以不存在)
导出的时候以schemas的方式导出 导入的时候使用 REMAP_SCHEMA=元用户名称:新导入的用户名称(可以不存在) 导出： expdp system/liups.com DIRECTORY=DATA_PUMP_DIR schemas=liups.com dumpfile=liups.com.dmp logfile=liups.com.log 导入： impdp system/liups.com DIRECTORY=DATA_PUMP_DIR dumpfile=liups.com.dmp REMAP_SCHEMA=liups.com:liups.org logfile=liups.com.log
- 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.
3、从一个表空间导入到另一个表空间
- REMAP_TABLESPACE : 重定义对象所在的表空间。 该参数用来重映射导入对象存储的表空间，支持同时对多个表空间进行 转换，相互间用逗号分割。语法如下： REMAP_TABLESPACE=Source_tablespace:Target_tablespace[,Source_tablespace:Target_tablespace] Warning: 如果使用Remap_tablespace参数，则要保证导入的用户对目标表空间有读写权限。
4、过滤数据 过滤数据主要依赖于Query和Sample两个参数。其中Sample参数主要针对expdp导出功能。 1. Query 与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:”Where id<5″,B:”Where name=’a’” 如果Where条件前没有指定Schema名或者表名的话，默认就是针对当前所有要被 导出的表。如：Query=Where id <5 Warning: 建议把Query参数放入到参数文件中使用，以避免转义符带来的麻烦。 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等都不会被导出。Warning: 建议把Exclude参数放入到参数文件中使用，以避免转义符带来的麻烦。 2. Include 正规则 与Exclude正好相反。指定包含的对象类型或者对象名称。 Warning: 由于两个参数功能正好相反，因此在执行导入或导出命令时，两个参数不能同时使用，否则Oracle也不知道你想要干什么啦。 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_clause子句可以为SQL表达式用于过滤特定的对象名字。它由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。 当未指定name_clause而仅仅指定object_type则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。 示例： expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (‘EMP’,’DEPT’)” impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:”=’EMP’” 常用的过滤SQL表达式 EXCLUDE=SEQUENCE,VIEW –过滤所有的SEQUENCE,VIEW EXCLUDE=TABLE:”IN (‘EMP’,’DEPT’)” –过滤表对象EMP,DEPT EXCLUDE=SEQUENCE,VIEW,TABLE:”IN (‘EMP’,’DEPT’)” –过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT EXCLUDE=INDEX:”= ‘INDX_NAME’” –过滤指定的索引对象INDX_NAME INCLUDE=PROCEDURE:”LIKE ‘PROC_U%’” –包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符) INCLUDE=TABLE:”> ‘E’ “ –包含大于字符E的所有表对象 其它常用操作符 NOT IN, NOT LIKE, <, != 等等 直接将过滤操作符封装到参数文件中，如下面的例子 Parameter file:exp_scott.par DIRECTORY = dump_scott DUMPFILE = exp_scott_%U.dmp LOGFILE = exp_scott.log SCHEMAS = scott PARALLEL= 2 EXCLUDE = TABLE:”IN (‘EMP’, ‘DEPT’)” expdp system/manager parfile=exp.par 命令行下转义符的处理 Windows平台: D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\“IN (‘EMP’, ‘DEPT’)\“ 在Windows平台下，需要对象双引号进行转义，使用转义符\ Unix平台: 在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等 % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\“IN \(\‘EMP\‘, \‘DEP\‘\)\“ exclude/include常见的错误 任意需要转义的字符如果未转义或转义错误，都会产生ORA错误。下面给出几种常见的ORA错误。 ORA-39001: invalid argument value ORA-39071: Value for INCLUDE is badly formed. ORA-00936: missing expression ORA-39001: invalid argument value ORA-39071: Value for EXCLUDE is badly formed. ORA-00904: “DEPT”: invalid identifier ORA-39001: invalid argument value ORA-39041: Filter “INCLUDE” either identifies all object types or no object types. ORA-39001: invalid argument value ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types ORA-39001: invalid argument value ORA-39038: Object path “USER” is not supported for TABLE jobs. 高级过滤 在导出/导入的时候，我们常常有这样的需求，只想导出/导入表结构，或者只想导出/导入数据。幸运的是数据泵也提供了该功能。使用 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参数无效。
原文作者: liups.com
原文链接: http://liups.cn/posts/810cb432/
许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议