数据泵详解及案例

数据泵详解及案例
北京神州泰岳软件股份有限公司2011年5月12日
文档属性
文档变更
文档送呈
目录
1前言 (5)
2准备工作 (6)
3导出常见需求案例 (7)
3.1全库导出 (7)
3.1.1全库导出最简写法 (7)
3.1.2全库并行导出到多个文件 (7)
3.1.3全库导出到多个路径 (7)
3.2按schema导出 (8)
3.3按表导出 (8)
3.3.1导出普通表 (8)
3.3.2导出表分区 (8)
3.4导出表空间 (9)
3.5部分导出(EXCLUDE与INCLUDE) (9)
3.5.1屏蔽部分schema (9)
3.5.2屏蔽部分对象 (9)
3.5.3屏蔽统计信息 (10)
3.5.4INCLUDE对象 (10)
3.6带查询条件导出(QUERY) (10)
3.7估算导出数据大小 (10)
3.8只导出元数据或数据 (11)
3.9其他可选参数 (11)
3.9.1JOB_NAME参数 (11)
3.9.2LOGFILE参数 (11)
3.9.3VERSION参数 (11)
3.9.4STA TUS参数 (12)
3.9.5PARFILE参数 (12)
3.9.6SAMPLE参数 (12)
4导入常见需求案例 (13)
4.1导入到不同SCHEMA中 (13)
4.2导入到不同表空间中 (13)
4.3将元数据以sql形式写入文本文件 (14)
4.4导入时使用新存储属性 (14)
4.5导入表存在时的处理 (14)
5管理维护任务 (15)
6重建数据泵工具 (17)
1 前言
Oracle 10g引入了最新的数据泵（Data Dump）技术，使DBA或开发人员可以将数据库元数据（对象定义）和数据快速移动到另一个oracle数据库中。

数据泵导出导入（EXPDP和IMPDP）的作用：
1)实现逻辑备份和逻辑恢复。

2)在数据库用户之间移动对象。

3)在数据库之间移动对象
4)实现表空间搬移。

数据泵导出导入与传统导出导入的区别：在10g之前，传统的导出和导入分别使用EXP 工具和IMP工具，从10g开始，不仅保留了原有的EXP和IMP工具，还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项；EXP和IMP是客户端工具程序，它们既可以在可以客户端使用，也可以在服务端使用。

EXPDP和IMPDP是服务端的工具程序，他们只运行在ORACLE服务端，不受连接会话是否断开的影响。

数据泵与传统导入导出产生的dump文件互不兼容，即使用EXPDP导出的文件，无法使用IMP导入，而以EXP导出的文件，无法用IMPDP导入。

在实际工作中，值得注意的是源库字符集尽量与目标库字符集相同。

本文档适用于Oracle10.2版本的数据泵。

2 准备工作
使用数据泵除了要求数据库用户有足够的系统权限，还需要建立directory与操作系统目录想对应，并授予用户对该对象的读写权限。

执行数据泵程序的数据库用户需要有CREATE SESSION, CREATE TABLE, EXP_FULL_DATABASE, IMP_FULL_DATABASE等几个系统权限。

通过如下命令在数据库中建立directory并与操作系统的目录相对应：
CREATE DIRECTORY 自定义名称 AS '系统目录的全路径';
GRANT read,write ON DIRECTORY 自定义名称TO 用户;
举例如下：
CONNECT system/manager
CREATE DIRECTORY my_dir AS '/backup';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO scott;
ALTER USER scott DEFAULT ROLE all;
ALTER USER scott QUOTA unlimited ON users;
相关视图：dba_sys_privs，dba_role_privs，dba_directories
3 导出常见需求案例
其中重要参数用红字标出
3.1 全库导出
3.1.1 全库导出最简写法
使用FULL 参数导出全库数据
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp FULL=y
DIRECOTRY 是数据库中directory 的名称
DUMPFILE 是导出文件名
FULL 代表全库导出
3.1.2 全库并行导出到多个文件
当导出单个文件过大时，可使用多个文件，并配合FILESIZE 参数限制单个文件大小 expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp,demo02.dmp,d emo03.dmp FULL=y FILESIZE=20g PARALLEL=2
FILESIZE 是每个导出文件的最大尺寸，可接受单位为b,k,m,g ，默认是b--字节
PARALLEL 是并行度，默认为1，当数据库为企业版并且CPU 数目为多个时可设置超过1，与文件个数配合可提高导出速度。

该参数可在导出过程中修改3.1.3 全库导出到多个路径。

如果文件个数无法预估，可使用DUMPFILE=%U ，将自动生成导出文件，如：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo%U FULL=y FILESI ZE=20g PARALLEL=2
有时路径所在文件系统的容量不足以存放导出文件，可以将导出文件分散在多个路径所
在文件系统下，如：
expdp scott/tiger DUMPFILE=data_pump_dir:demo01.dmp,other_dir:demo02.dmp FULL =y FILESIZE=20g PARALLEL=2
注意该语句没有使用DIRECTORY参数，而是在DUMPFILE中设置
other_dir为另一个directory，同样需要给用户授予读写权限
3.2 按schema导出
如果数据库只有少数schema（可简单理解为用户的所有对象），可以使用SCHEMAS 参数进行导出，以提高导出速度并减少问题几率，如：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test
SCHEMAS为需要导出的用户，用逗号隔开
如果导出数据较多，需要分割文件或分散路径，参照3.1全库导出
3.3 按表导出
3.3.1 导出普通表
如果只需要导出个别表，则使用TABLES参数，如：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp TABLES=test, dept
注意导出的默认是当前用户的表，TABLES、SCHEMAS、FULL不可同时使用，如果想导出其他用户的表，需要在表前加用户的名字，如：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp TABLES=sc ott.test
3.3.2 导出表分区
如果想导出表的某个分区的数据，使用如下命令：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp TABLES=test: p2*******
3.4 导出表空间
expdp scott/tiger DUMPFILE=data_pump_dir:demo01.dmp TABLESPACES=users
3.5 部分导出(EXCLUDE与INCLUDE)
exclude参数和include参数分别用来屏蔽不需要的数据和导出需要的数据。

当使用exclude屏蔽某对象时，依赖这个对象的其他对象也一并被屏蔽，比如exclude一张表，则其索引和触发器都被屏蔽。

exclude和include参数互斥，无法一起使用，但其语法相同。

3.5.1 屏蔽部分schema
例如想要屏蔽scott用户的所有数据，使用如下命令：
expdp scott/tiger FULL=y DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp EX CLUDE=SCHEMA:\"like \'SCOTT\'\"
exclude参数中的“\”为转义符，在unix系统中使用，是为了正确识别引号。

而windows 中不需要。

“like”也可替换成“=”。

如果屏蔽多个schema，使用如下命令
expdp scott/tiger FULL=y DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp EX CLUDE=SCHEMA:\"IN\(\'SCOTT\',\'TKF\'\)\"
由上可知，除了逗号，其他符号都需要由转义符“\”来识别。

3.5.2 屏蔽部分对象
可以屏蔽的主要对象包括：table,function,package,function,procedure,sequence,grant,view, index,db_link,constraint,ref_constraint,job,statistics等，语法如下：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott EXCLUDE=procedure,function,table:\"IN \(\'TSUMSCHEMA\',\'TSUMALGORITHM \'\)\"
其中对象如果名字类似，可以使用通配符“%”，如下：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott EXCLUDE=table:\"like \'TE%\'\"
3.5.3 屏蔽统计信息
统计信息包含表的平均行长，行数，字段直方图信息等，也是可以用exclude屏蔽的对象。

之所以把它单拿出来，是因为数据泵有时会因为统计信息的问题导致导出时间过长，甚至失败。

我们完全可以在导出全库或较大schema时屏蔽统计信息，待导入完成再手工搜集。

其语法为：
expdp scott/tiger FULL=y DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp EX CLUDE=statistics
3.5.4 INCLUDE对象
include的语法与exclude相同，作用相反，参照以上即可。

值得注意的是，导出及导入job对象时，不应指定schema参数：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp INCLUDE=job 3.6 带查询条件导出(QUERY)
有时需要导出表中符合一定条件的数据，可以使用query参数。

exp和expdp的用法稍有不同，exp里只允许以tables方式使用，而expdp可以在tables,schemas,full三种方式导出时使用query选项。

如要导出scott用户所有表的10行数据，如下：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott QUERY=\"where rownum\<11\"
注意在unix里，特殊符号前都需要加转义符“\”
3.7 估算导出数据大小
expdp scott/tiger DIRECTORY=data_pump_dir SCHEMAS=scott ESTIMATE=statistics ESTIMATE_ONL Y=y
ESTIMATE有两个选项：block和statistics，默认是block，即数据块的多少；statistics 根据统计信息计算导出数据的大小，为了精确，在导出前分析各个表。

ESTIMATE_ONL Y默认是n，如果选y则不进行数据导出，只估算大小。

注意当使用此参。

数时，无法同时使用DUMPFILE参数
3.8 只导出元数据或数据
数据泵默认导出元数据及数据数据，当只希望导出数据库结构时，可使用content参数：expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp FULL=y cont ent=metadata_only
content除了metadata_only外还有两个选项：all和data_only，其中all是默认选项，data_only 只导出数据。

3.9 其他可选参数
3.9.1 JOB_NAME参数
该参数用于自定义任务名称，即使不使用该参数，系统也会自动生成一个：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp FULL=y JOB _NAME=expfull
任务名字长度不可超过30个字符
3.9.2 LOGFILE参数
该参数用于指定导出任务的日志文件，即使不使用该参数，系统也会自动生成一个名为export.log的日志文件，但当执行多个导出任务时，该文件将被覆盖：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp FULL=y LOGFIL E=expfull.log
3.9.3 VERSION参数
该参数用于版本间互相兼容，当目标库的版本较低时，需用该参数指定版本，该版本应等于或小于目标库：
expdp scott/tiger DIRECTORY=data_pump_dir SCHEMAS=scott VERSION=10.1.0
3.9.4 STATUS参数
使用该参数时将更新任务状态，单位是秒，默认为0：
expdp scott/tiger DIRECTORY=data_pump_dir SCHEMAS=scott STA TUS=20
3.9.5 PARFILE参数
该参数用于指定参数文件，可事先建立参数文件，比如parfile.par，格式如下：
TABLES=servers DUMPFILE=data_pump_dir:demo01.dmp
LOGFILE=data_pump_dir:demo01.log
PARALLEL=2
则使用该文件语句为：
expdp scott/tiger PARFILE=data_pump_dir:parfile.par
3.9.6 SAMPLE参数
如果只希望按一定比例导出数据，可以使用sample参数：
expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott SAMPLE=10
SAMPLE是希望导出数据的百分比，其原有格式是：
SAMPLE=[[schema_name.]table_name:]sample_percent
即可以对某schema的某张表进行百分比导出
4 导入常见需求案例
通常导入与导出的语法相同，比如导入全库：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp FULL=y
导入schema:
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test
导入部分对象
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott EXCLUDE=procedure,function,table:\"IN \(\'TSUMSCHEMA\',\'TSUMALGORITHM \'\)\"
导入job
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp INCLUDE=job 下面主要介绍impdp与expdp不同的参数：
4.1 导入到不同SCHEMA中
有需要将各种对象从一个schema导入到另外一个schema中，这时需要使用参数REMAP_SCHEMA，如：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test REMAP_SCHEMA=scott:tkf
上述语句可将scott用户下所有对象导入到tkf用户下
4.2 导入到不同表空间中
如果想重新映射对象的表空间，可使用REMAP_TABLESPACE参数，如：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test REMAP_TABLESPACE=users:tpubak
4.3 将元数据以sql形式写入文本文件
如果希望将dump的文件中ddl语句提取出来，可使用SQLFILE参数，如：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test SQLFILE=scotttest.sql
该语句只把dump文件中的ddl语句写入scottest.sql文件中，可根据需要查看并使用。

4.4 导入时使用新存储属性
如果导入时希望使用新的表空间存储参数，或表的存储字句，可使用TRANSFORM参数，例：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test TRANSFORM=SEGMENT_ATTRIBUTES:n:table
该命令将使用新表空间的存储字句
TRANSFORM参数的语法如下：
TRANSFORM = transform_name:value[:object_type]
其中transform_name有SEGMENT_ATTRIBUTES, STORAGE,OID, PCTSPACE四种。

4.5 导入表存在时的处理
如果要导入的表已经存在，则使用TABLE_EXISTS_ACTION参数处理：
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=demo01.dmp SCHEMAS=s cott,test TABLE_EXISTS_ACTION=truncate
TABLE_EXISTS_ACTION有四个选项：
SKIP（默认），跳过；
APPEND，插入到表后；
REPLACE，替换原表；
TRUNCATE，截断原表并插入。

5 管理维护任务
由于数据泵是服务器端程序，即使连接会话断开，也不妨碍任务的继续。

可以通过ATTACH参数来连接仍在运行的任务，语法如下：
expdp scott/tiger attach=expscott
其中expscott为job_name，是导出时指定的任务名。

如果导出时未指定任务名，可使用dba_datapump_jobs视图来查看当前数据泵任务名及状态
select owner_name,job_name,operation,job_mode,state from dba_datapump_jobs;
把job_name 填写到attach参数的后面即可连接到该任务。

连接到正在运行的任务后，可以使用如下命令：
命令说明(默认)
------------------------------------------------------------------------------
CONTINUE_CLIENT 返回到记录模式。

如果处于空闲状态, 将重新启动作业。

EXIT_CLIENT 退出客户机会话并使作业处于运行状态。

HELP 总结交互命令。

KILL_JOB 分离和删除作业。

PARALLEL 更改当前作业的活动worker 的数目。

PARALLEL=<worker 的数目>。

START_JOB 启动/恢复当前作业。

START_JOB=SKIP_CURRENT 在开始作业之前将跳过
作业停止时执行的任意操作。

STA TUS 在默认值(0) 将显示可用时的新状态的情况下,
要监视的频率(以秒计) 作业状态。

STATUS[=interval]
STOP_JOB 顺序关闭执行的作业并退出客户机。

STOP_JOB=IMMEDIATE 将立即关闭
数据泵作业。

其中KILL_JOB同时删除任务记录，STOP_JOB并不删除记录。

记录需要通过drop table job_name来删除。

查看活动的数据泵进程
SQL> desc dba_datapump_sessions
名称是否为空? 类型
----------------------------------------- -------- -----------------
OWNER_NAME V ARCHAR2(30) JOB_NAME V ARCHAR2(30) SADDR RAW(8) SESSION_TYPE V ARCHAR2(14)
SQL> select * from dba_datapump_sessions;
未选定行
6 重建数据泵工具
有时数据泵被损坏需要被重建，以避免内部错误。

对于10.2和11.1版本，使用如下脚本：
1、重建数据泵类型和视图：
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
2、重建tde_library packages
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb
3、重建数据泵的package
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql
4、重建数据泵对象
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
5、重新编译无效对象
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql。