ORACLE 进阶(六)ORACLE expdp/impdp 详解
一、ORACLE 数据泵
ORCALE10G
提供了新的导入导出工具,数据泵。
Oracle 官方对此的形容是:
Oracle DataPump technology enables Very High-Speed movement of data and metadata from one database to another.
其中 Very High-Speed 是亮点。
先说数据泵提供的主要特性(包括,但不限于):
支持并行处理导入、导出任务;
支持暂停和重启动导入、导出任务;
支持通过
Database Link
的方式导出或导入远端数据库中的对象;支持在导入时通过
Remap_schema、Remap_datafile、Remap_tablespace
几个参数实现导入过程中自动修改对象属主、数据文件或数据所在表空间;导入/导出时提供了非常细粒度的对象控制。通过
Include、Exclude
两个参数,甚至可以详细制定是否包含或不包含某个对象。
二、什么是 Directory 对象
Directory
对象是Oracle10g
版本提供的一个新功能。他是一个指向,指向了操作系统中的一个路径。每个 Directory 都包含 Read,Write 两个权限,可以通过 Grant 命令授权给指定的用户或角色。拥有读写权限的用户就可以读写该 Directory 对象指定的操作系统路径下的文件。
除了使用
network_link
参数意外,expdp
生成的文件都是在服务器上(Directory 指定的位置)
2.1 如何调用
命令行方式最简单的调用,但是写的参数有限,建议使用参数文件的方式。
参数文件方式最常用的方式。通常需要先编写一个参数文件。指定导出时需要的各种参数。然后以如下方式调用。
这个 xxx.par 即是编辑的参数文件。注意,在这个命令行后面,同样可以再跟别的参数, 甚至是在 par 参数文件中指定过的参数。
如果执行命令中附加的参数与参数文件中的参数有重复,最终采用哪个参数,会以参数最后出现的位置而定。如:expdp user/pwd parfile=xxx.par logfile=a.log
,如果在参数文件中也指定了 logfile,这里会以命令行中的 logfile 为准;如:expdp user/pwd logfile=a.log parfile=xxx.par
,而这个,则会以参数文件中的为准,因为 parfile=xxx.par 写在命令行的后面。
交互方式
Data Pump
导入导出任务支持停止,重启等状态操作。如用户执行导入或者导出任务,执行了一半时,使用 Crtl+C 中断了任务(或其他原因导致的中断),此时任务并不是被取消,而是被转移到后台。可以再次使用 expdp/impdp 命令,附加 attach 参数的方式重新连接到中断的任务中,并选择后续的操作。 这就是交互方式。
2.2 什么是 attach 参数
每执行一个导入,或者导出,在命令的第一行,会有以下信息:
这个SYS_EXPORT_SCHEMA_01
就是我们的 attach 参数。
三、操作模式
全库模式导入或者导出整个数据库,对应
impdp/expdp
命令中的 full 参数,只有拥有 dba 或者exp_full_database
和imp_full_database
权限的用户才能执行。Schema 模式导出或导入 Schema 下的自有对象,对应
impdp/expdp
命令中的 Schema 参数,这是默认的操作模式。 如果拥有 dba 或者exp_full_database
和imp_full_database
权限的用户执行的话,就可以导出或导入多个Schema
中的对象。表模式导出指定的表或者表分区(如果有分区的话)以及依赖该表的对象(如该表的索引,约束等,不过前提是这些对象在同一个
Schema
中,或者执行的用户有相应的权限)。对应impdp/expdp
命令中的 Table 参数。表空间模式导出指定的表空间中的内容。对应
impdp/expdp
中的Tablespaces
参数,这种模式类似于表模式和 Schema 模式的补充。传输表空间模式对应
impdp/expdp
中的Transport_tablespaces
参数。这种模式与前面几种模式最显著的区别是生成的 Dump 文件中并不包含具体的逻辑数据,而只导出相关对象的元数据(即对象的定义,可以理解成表的创建语句),逻辑数据仍然在表空间的数据文件中,导出时需要将元数据和数据文件同时复制到目标端服务器。这种导出方式效率很高,时间开销主要是花在复制数据文件产生的 I/O 上。expdp 执行传输表空间模式的导出,用户必须 拥有 exp_full_database 角色或者 DBA 角色。而通过传输表空间模式导入时,用户必须拥有 imp_full_database 角色或者 DBA 角 色。过滤数据过滤数据主要依赖于 Query 和 Sample 两个参数。其中 Sample 参数主要针对 expdp 导出功能。
Query 与 exp 命令中的 Query 功能类似,不过 Expdp 中,该参数功能得到了增强,控制的粒度更细。Expdp 中的 Query 也是指定类似 where 语句来限定记录。语法如下:
默认情况如果不指定Schema.table_name
,则Query_clause
针对所有导出的表有效,或者你可以为每一个表指定不同的Query_clause
,如:导出 a 表中所有 id<5 的记录,导出 b 表中所有 name=’a’的记录,则 Query 的参数应该如下:
如果Where
条件前没有指定Schema
名或者表名的话,默认就是针对当前所有要被导出的表。如:Query=Where id <5
注⚠️: 建议把 Query 参数放入到参数文件中使用,以避免转义符带来的麻烦。
Sample 该参数用来指定导出数据的百分比,可指定的值的范围从 0.000001 到 99.999999,语法如下:
指定该参数以后,EXPDP 导出将自动控制导出的记录量,如导出 A 表中 50%的记录,设置的 Sample 参数如下:Sample=A:50 注: Sample_percent 指定的值只是一个参考值,EXPDP 会根据数据量算出一个近似值。
四、过滤对象
过滤对象主要依赖于 Include 和 Exclude 两个参数。这两个参数作用正好相反,在这两个参数中,可以指定你知道的任何对象类型(如:Package、Procedure、Table 等等)或者对象名称(支持通配符)。
Exclude 反规则
指定不被包含的对象类型或者对象名称。指定了该参数以后,指定的对象类型对应的所有对象都不会被导入或导出。 如果被排除的对象有依赖的对象,那么其依赖的对象也不会被导入或导出。如:通过 Exclude 参数指定不导出表对象的话,不仅指定的表不会被导出,连这些表关联的 Index、Check 等都不会被导出。注: 建议把 Exclude 参数放入到参数文件中使用,以避免转义符带来的麻烦。
Include 正规则
与Exclude
正好相反。指定包含的对象类型或者对象名称。
注⚠️: 由于两个参数功能正好相反,因此在执行导入或导出命令时,两个参数不能同时使用,否则 Oracle 也不知道你想要干什么。
exclude/include
参数用法:
object_type
子句用于指定对象的类型,如table,sequence,view,procedure,package
等等
name_clause
子句可以为 SQL 表达式用于过滤特定的对象名字。它由 SQL 操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。
当未指定name_clause
,而仅仅指定object_type
则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。示例:
常用的过滤 SQL 表达式
直接将过滤操作符封装到参数文件中,如下面的例子
命令行下转义符的处理 Windows 平台:
在 Windows 平台下,需要对象双引号进行转义,使用转义符\
Unix 平台:在未使用 parfile 文件的情形下,所有的符号都需要进行转义,包括括号,双引号,单引号等
exclude/include 常见的错误任意需要转义的字符如果未转义或转义错误,都会产生 ORA 错误。下面给出几种常见的 ORA 错误。
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: “DEPT”: invalid identifier
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types
ORA-39038: Object path “USER” is not supported for TABLE jobs.
五、高级过滤
在导出/导入的时候,我们常常有这样的需求,只想导出/导入表结构,或者只想导出/导入数据。幸运的是数据泵也提供了该功能。使用 Content 参数。该参数有三个属性
ALL
: 导出/导入对象定义和数据,该参数的默认值就是 ALLDATA_ONLY
: 只导出/导入数据。METADATA_ONLY
: 只导出/导入对象定义。
注: 有一点值得注意的是,在执行导出的时候,如果使用了高级过滤,如只导出了数据,那么导入时,需要确保数据定义已经存在。否则数据都变成没有主子了。如果数据定义已经存在,导入时最好指定 data_only,否则会触发 ORA-39151 错误,因为对象已经存在了。
5.1 过滤已经存在的数据
我们知道,导入的表对象在目标库中已经存在,并且目标端没有创建数据完整性约束条件(RI)来检验数据的话,就有可能造成数据被重复导入。数据泵提供了一个新的参数Table_exists_action
,可以一定程度上降低重复数据的产生。该参数用来控制如果要导入的表对象存在,执行什么操作。有以下几个参数值:
SKIP
: 跳过该表,继续处理下一个对象。该参数默认就是SKIP
。值得注意的是,如果你同时指定了CONTENT
参数为Data_only
的话,SKIP
参数无效,默认为APPEND
。
APPEND
: 向现有的表中添加数据。
TRUNCATE
:TRUNCATE
当前表,然后再添加记录。使用这个参数需要谨慎,除非确认当前表中的数据确实无用。否则可能造成数据丢失。
REPLACE
: 删除并重建表对象,然后再向其中添加数据。值得注意的是,如果同时指定了CONTENT
参数为Data_only
的话,REPLACE
参数无效。
5.2 重定义表的 Schema 或表空间
我们还可能会遇到这样的需求,把 A 用户的对象转移到 B 用户,或者更换数据的表空间。数据泵通过Remap_Schema
和Remap_tablespace
参数实现了该功能。
REMAP_SCHEMA
: 重定义对象所属 Schema 该参数的作用类似 IMP 中的 Fromuser+Touser,支持多个 Schema 的转换,语法如下:
如把 A 的对象转换到 b 用户,将 C 转换到 D 用户。Remap_schema=a:b,c:d
注⚠️: 不能在同一个 IMPDP 命令中指定remap_schema=a:b,a:c.
REMAP_TABLESPACE
: 重定义对象所在的表空间。该参数用来重映射导入对象存储的表空间,支持同时对多个表空间进行转换,相互间用逗号分割。语法如下:
注⚠️: 如果使用Remap_tablespace
参数,则要保证导入的用户对目标表空间有读写权限。
5.3 优化导入/导出效率
对于大数据量来说,我们不得不考虑效率问题。数据泵对效率也提出了更高的要求。甚至官方的描述就是
这里的 Very High-Speed 依赖我们的 parallel 参数。
所有的优化操作都会有三种结果:变得更好、没有变化、变得更差。Parallel
参数也是这样,并不是指定一个大于 1 的参数,性能就会有提升。
对于导出的
parallel
对于导出来说,由于dump
文件只能由一个线程进行操作(包括 I/O 处理),因此如果输出的 DUMP 文件只有一个,即使你指定再多的并行,实际工作仍然是一个,而且还会触发 ORA-39095 错误。因此,建议设置该参数小于或等于生成的DUMP
文件数量。那么,如何控制生成的DUMP
文件数量呢?
EXPDP
命令提供了一个FILESIZE
参数,用来指定单个DUMP
文件的最大容量,要有效的利用parallel
参数,filesize
参数必不可少。
举例:某用户对象占用了 4G 左右的空间,实际导出后的 DUMP 文件约为 3G,我们尝试在导出该用户时指定并行度为 4,设置单个文件不超过 500M,则语法如 下:
对于导入的
parallel
对于导入来说,使用parallel
参数则要简单的多,导入更能体现parallel
参数的优势。 参数设置为几,则认为同时将几张表的内容导入到库中。
举例:某 dmp 文件中包含了 200 张表,我们尝试在导入该 DMP 文件时指定并行度为 10,则 语法如下:
版权声明: 本文为 InfoQ 作者【No Silver Bullet】的原创文章。
原文链接:【http://xie.infoq.cn/article/6bc277d338644c217273e1abb】。文章转载请联系作者。
评论