数据泵导出导入

出于许多原因，我们期望从某个数据库中提取大量数据以及关联的对象定义，并且采用一种更容易的方式将这些数据载入到另一个数据库中。备份就是其中一个重要原因，另外可能需要在生产与测试环境之间或联机系统与数据仓库之间转移数据。数据泵Data Pump是用于在oracle数据库间进行大规模、快速数据传输的工具。

 

1、Data Pump的体系结构

 

Data Pump是一个服务器端的实用程序，与过去的Export/Import实用程序相比，运行在服务器上的Data Pump进程直接访问数据文件和SGA，不必通过会话进行访问，可以显著的改善性能。

 

启动一个Data Pump作业时，至少会启动下列两个进程：一个Data Pump Master进程（DMnn），一个或多个工作者进程（DWnn）。主进程控制工作者进程。如果启用并行技术，那么每个DWnn进程都可以使用两个或多个并行执行服务器（Pnnn）。

 

每个Data Pump作业创建两个队列：一个控制队列和一个状态队列。DMnn进程将任务放置到控制队列，DWnn进程获取并执行任务，同时可能利用并行执行服务器。状态队列用于完成作业的监视功能，由DMnn进程在该队列中放置作业的状态消息。具有适当权限的任何会话都可以通过查询这个队列来监视作业的进度。

 

Data Pump生成的文件具有下列三种：sql文件、转储文件、日志文件。

 

2、目录和文件位置

 

Data Pump始终使用Oracle目录，用于定位要读写的文件及其日志文件。如果没有在Data Pump中指定目录，那么将使用默认目录。每个11g数据库都有一个可供使用的Oracle目录，其名称为DATA_PUMP_DIR。

 

可在四个级别上指定用户Data Pump作业的目录。按照优先级从高到低的顺序为：

• Data Pump 作业中每个文件的设置
• 应用于整个 Data Pump 作业的参数
• DATA_PUMP_DIR 环境变量
• DATA_PUMP_DIR 目录对象

因此，可以明确控制每个文件的位置，可以为作业指定单个Oracle目录，可以使用环境变量，如果这些都行不通，Data Pump将使用默认目录。

 

以下创建一个自己的目录对象

create directory my_dir as 'e:\my_dir';

给目录授权

grant read, write on directory my_dir to public;

确认目录已创建

col directory_path for a60;

select * from dba_directories;

 

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------------------------------------

SYS                            MY_DIR                         e:\my_dir

SYS                            ORACLECLRDIR                   C:\oracle\app\oracle\product\11.2.0\server\bin\clr

SYS                            DATA_PUMP_DIR                  C:\oracle\app\oracle/admin/xe/dpdump/

SYS                            XMLDIR                         C:\oracle\app\oracle\product\11.2.0\server\rdbms\xml

SYS                            ORACLE_OCM_CONFIG_DIR          C:\ADE\aime_xe28\oracle/ccr/state

 

3、直接路径和外部表路径

 

Data Pump通过直接路径或外部表路径这两种方式来完成数据的加载和卸载。

 

直接路径避开了数据库缓冲区缓存。通过直接路径导出数据时，Data Pump从磁盘直接读取数据文件，提取和格式化文件内容，最后将这些文件内容写为一个转储文件。通过直接路径导入数据时，Data Pump读取转储文件，使用文件内容填充表数据块，最后将这些内容直接写入数据文件。写操作在表的“高水位线”之上完成。

 

外部表路径使用了数据库缓冲区缓存。导出数据时，Data Pump使用普通的SELECT进程将数据块从数据文件中读入缓存，为了输出为一个转储文件，会格式化数据。导入数据时，Data Pump根据转储文件的内容构造标准的INSERT语句，并且通过将数据块从数据文件读至缓存来执行这些语句，INSERT操作按照标准的样式在缓存中完成。如同普通的DML一样，使用外部表路径时也会同时生成撤销和重做。

 

那么怎样才能确定Data Pump是使用直接路径还是外部表路径呢？DBA并不能对此控制，Data Pump会根据对象的复杂性做出决定。只有简单的结构（如不具有活动触发器的堆表）才能通过直接路径处理。对于更复杂的对象（如群集表），为了分解复杂性而要求和SGA进行交互，此时必须使用外部表路径。在这两种情况下，生成的转储文件是完全相同的。

 

4、使用Data Pump导出和导入

 

Data Pump可以导出完整的数据库，或数据库的一部分内容。可以导出包含或不包含数据行的表定义、PL/SQL对象、视图、序列或其他任何对象类型。如果导出的是表，还可以用WHERE子句限制导出的行，或导出表的随机百分比抽样。

 

可以使用并行处理加快Data Pump的操作速度。并行有两种级别：工作者进程数量，以及每个工作者进程使用的并行服务器数量。

 

其评估功能可以在不实际运行作业的情况下，计算Data Pump导出需要的空间。

 

在网络模式下，即使没有在磁盘上分段传输数据，仍然可以使用Data Pump将数据集从一个数据库转移到另一个数据库。其实现方式是：源数据库上的Data Pump导出作业通过数据库链接，将数据写入目标数据库，而Data Pump导入作业从数据库链接读取数据，并插入数据。网络模式下不需要转储文件目录，但还是需要日志文件目录的。

 

重新映射功能可以重命名对象，可以将对象从一个模式传输到另一个模式，还可以在导入时将数据对象从一个表空间移动到另一个表空间。

 

导出时可以压缩和加密输出文件。

 

导出导入登录操作的用户必须具有exp_full_database或imp_full_database的系统权限。

 

以下列举一些常见的用法：

 

1）导出导入整个数据库

 

要导出整个数据库，可使用如下命令：

expdp system/mesHz2@mes full=y directory=my_dir parallel=4 dumpfile=full1_%U.dmp,full2_%U.dmp,full3_%U.dmp,full4_%U.dmp filesize=2g compression=all

此命令以system用户登录数据库，并使用4个并行运行的工作者进程，每个工作者进程将生成自己的转储文件集合，这些转储文件根据%U模板命名，它将生成唯一的字符串。每个工作者进程将输出分解为多个不超过2G大小的压缩文件。

选项compression取值方法对于10g和11g有所不同，10g未描述此选项时默认为压缩。

 

对应的导入：

impdp system/mes@mes full=y directory=my_dir parallel=4 dumpfile=full1_%U.dmp,full2_%U.dmp,full3_%U.dmp,full4_%U.dmp

在全库导入的情况下，导入前模式用户不必先建好，导入时系统会自动重建，只需建立好需要的表空间即可。

 

2）指定导出导入的模式

 

可以使用schemas选项指定导出导入的模式：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp filesize=2g compression=all

 

对应的导入：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp

 

3）仅导出元数据

 

导出RMES、BOSCH、ABS几个用户对象的元数据，不包含表的行数据：

expdp system/mesHz2@mes schemas=rmes,bosch,abs dumpfile=my_dir:dp_meta.dmp content=metadata_only

 

对应的导入：

impdp system/mesHz2@mes schemas=rmes,bosch,abs dumpfile=my_dir:dp_meta.dmp

 

4）包含和排除对象

 

将需要包含或排除的对象在include或exclude子句中列出，以下列出的都是数据泵作业中包含的对象类型（注意触发器包含在表类别）：

expdp system/mesHz2@mes schemas=cmes dumpfile=my_dir:dp_cmes.dmp include=user,system_grant,role_grant,default_role,pre_schema,synonym,type,sequence,table,package,function,procedure,view

 

导出可以按照条件只包含特定的对象和数据，排除不需要的对象。表的筛选条件可以是单张或多张表、用like指定的模糊表名，甚至可以通过SQL查询来指定表名，而数据可以通过where条件来指定：

expdp system/mesHz2@mes dumpfile=my_dir:1.dmp tables=rmes.r_wip_print_t

expdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:2.dmp include=table:"in('R_WIP_PRINT_T')"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=3.dmp exclude=TABLE;

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=4.dmp exclude=TABLE:\"LIKE \'TMP%\'\"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=5.dmp exclude=TABLE:\"IN \(\'C_LINE_T\'\,\'C_BOM_T\'\)\"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=6.dmp exclude=TABLE:\"IN \(select table_name from cmes.k$host_condition where condition <> 'N/A'\)\"

expdp system/mesHz2@mes dumpfile=my_dir:7.dmp tables=rmes.r_wip_tracking_t query="'where in_line_time<=to_date(''2015-01-31 23:59:59'',''yyyy-mm-dd hh34:mi:ss'')'"

 

如果需要同时导出多张表，并且按照不同条件导出，则可以把这些条件放入一个参数文件中，用参数文件来指定数据泵的导出作业，如下一点所述。

 

导入时可根据需要考虑是否要先将表truncate，保留其结构，再按条件导入数据：

impdp system/mesHz2@mes dumpfile=my_dir:dp.dmp content=data_only tables=rmes.r_wip_tracking_t query="'where in_line_time<=to_date(''2015-01-31 23:59:59'',''yyyy-mm-dd hh34:mi:ss'')'"

 

可以仅导出指定的表空间：

expdp system/mesHz2@mes directory=my_dir parallel=2 dumpfile=dp1_%u.dmp,dp2_%u.dmp filesize=2g tablespaces=rmes

 

5）指定数据泵作业的参数文件

 

整个作业还可以通过parfile选项指定到一个参数文件，便于以后的修改和操作。

 

如创建一个参数文件e:\wip_tracking.par，指定按不同条件导出多张表中的数据：

job_name=exp_wip_tracking

directory=data_pump_dir

dumpfile=wip_tracking.dmp

tables=rmes.r_wip_tracking_t,rmes.h_wip_tracking_t

query=rmes.r_wip_tracking_t:"where in_line_time between to_date('2015-02-01 00:00:00','yyyy-mm-dd hh34:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh34:mi:ss')",rmes.h_wip_tracking_t:"where in_line_time between to_date('2015-02-01 00:00:00','yyyy-mm-dd hh34:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh34:mi:ss')"

 

导出作业命令如下：

expdp system/mesHz2@mes parfile=e:\wip_tracking.par

 

参数文件的指定必须使用绝对路径。

 

6）给作业定义一个名称

 

可以给作业自定义一个名称，这样以后可以通过交互模式管理数据泵作业

expdp system/mesHz2@mes directory=my_dir parallel=2 dumpfile=dp1_%u.dmp,dp2_%u.dmp filesize=2g tablespaces=rmes job_name=exp_rmes_tbs

 

7）导入时重映射

 

可以在导入时通过remap_schema选项对模式做重新映射，通过remap_tablespace选项对表空间做重新映射，通过remap_datafile选项对数据文件做重新映射。

 

如下映射进行模式的迁移：

impdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:dp_rmes.dmp remap_schema=rmes:cmes

 

如下映射进行表空间的迁移：

impdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:dp_rmes.dmp remap_tablespace=rmes:hmes

 

如下映射重定义数据文件的路径和名称：

impdp system/mesHz2@mes schemas=scott dumpfile=my_dir:dp_scott.dmp remap_datafile='d:\oradata\mes\users01.ora':'e:\oradata\mes\users01.dbf'

 

8）抽样数据导出

 

如下按每个用户每个表的10%的数据比例正态随机抽样导出：

expdp system/mesHz2@mes schemas=rmes,abs directory=my_dir parallel=4 dumpfile=dp1.dmp,dp2.dmp,dp3.dmp,dp4.dmp sample=10

 

9）转储导出文件中的DDL定义

 

可以从导出文件中将DDL定义导入到一个SQL文件中，并不执行真正的导入，这样可以知道备份中发生了哪些DDL行为：

impdp system/mesHz2@mes dumpfile=my_dir:1.dmp sqlfile=my_dir:1.sql

 

以下的一组数据泵操作可以按指定的筛选条件导出一部分表的DDL定义，其中包含了表的定义，表上索引、约束的定义，还有表的注释定义，也包含了表的统计信息：

expdp system/mesHz2@mes schemas=cmes dumpfile=my_dir:1.dmp content=metadata_only include=TABLE:\"IN \(select table_name from dis.dis_host_condition where condition is not null\)\"

impdp system/mesHz2@mes dumpfile=my_dir:1.dmp sqlfile=my_dir:1.sql

 

10）预估导出文件的大小

 

可以预估导出文件的大小，而不执行真正的导出：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs estimate_only=y

 

默认将使用blocks方法根据表占用的数据块数量乘上数据块大小来预估，也可以使用统计信息来预估，但统计信息要准确：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs estimate_only=y estimate=statistics

 

11）导入时过滤掉存储参数

 

可以使用transfORM选项，过滤掉表DDL定义中有关Storage存储参数的定义，解决导入时因存储参数中initial初始分配太大导致空间不足的问题：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=storage:n

 

如果将transform选项设定为segment_attributes:n，则导入时还会去掉表DDL定义中有关tablespace的定义，从而将所有表改用用户默认的表空间来存储：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=segment_attributes:n

 

12）导入时表已存在的处理

 

可以使用table_exists_action选项指定导入时如果表已存在时执行的操作，默认是skip（跳过操作），也可以选择append（在原来数据的基础上追加数据）、replace（表先drop掉，再重建，之后再插入数据）或者truncate（表先截断，再插入数据）：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=storage:n table_exists_action=replace

 

除了表的更新能够replace外，对于其它对象如存储过程的更新，并不会替换，而是跳过导入。

 

以下替换导入单张表及其数据：

impdp system/mesHz2@mes dumpfile=my_dir:mes.dmp tables=rmes.r_wip_keypart_t table_exists_action=replace

 

13）导出时覆盖重名文件

 

可以使用选项reuse_dumpfiles = y来覆盖重名文件：

expdp cles/cles schemas = cles directory = data_pump_dir dumpfile = cles.dmp reuse_dumpfiles = y job_name = expdp_cles

 

14）导出带有日期时间格式的文件名

 

可以使用%date:~n1,n2%和%time:~n1,n2%这样的变量形式定义导出带有日期时间格式的文件名：

expdp cles/cles schemas = cles directory = data_pump_dir dumpfile = cles%date:~2,2%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%.dmp reuse_dumpfiles = y job_name = expdp_cles

 

以上导出的文件名格式形如CLES1901131806.DMP这样的名称。

 

15）通过数据库链接导出导入

 

如果本地也建有数据库，则可以通过网络访问远程数据库服务端。此时本地作为客户端，需要在本地tnsnames中建立对应的网络服务名，指向远程数据库服务端。之后通过如下命令创建数据库链接对象：

create public database link dl_mes connect to system identified by mesHz2 using 'mes_1';

这里创建了数据库链接对象dl_mes，并设定以用户名system和密码mesHz2连接远程数据库，网络服务名为mes_1。

 

查看创建的数据库链接对象：

col object_name for a30

select owner, object_name from dba_objects where object_type='DATABASE LINK';

 

OWNER                          OBJECT_NAME

------------------------------ ------------------------------

PUBLIC                         DL_MES

 

详细的信息则可以通过dba_db_links查看：

col owner for a20

col db_link for a20

col username for a20

col host for a20

select * from dba_db_links;

 

OWNER                DB_LINK              USERNAME             HOST                 CREATED

-------------------- -------------------- -------------------- -------------------- -----------

PUBLIC               DL_MES               SYSTEM               mes_1                2016/11/19

 

可以通过数据库链接对象访问远程机器：

select * from scott.emp@dl_mes;

 

可以通过数据库链接对象执行远程数据库的expdp导出，dump文件到本地目录：

expdp system/mesHz2@mes network_link=dl_mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp filesize=2g

 

甚至可以省去expdp这一步，直接通过impdp将远程数据库对象导入到本地系统：

impdp system/mesHz2@mes network_link=dl_mes schemas=cmes,rmes,bosch,abs

 

16）指定是否需要创建日志

 

数据泵作业默认会生成日志文件，如果不想生成日志文件，可以通过nologfile选项指定：

impdp system/mesHz2 network_link=dl_mes_0 schemas=cmes include=procedure nologfile=y

 

17）会话交互模式

 

数据泵的导出导入可以切换到会话交互模式下进行，此时用户可以干预导出导入过程，穿插进前台的会话而不影响后台的数据泵作业。

 

在导出导入过程中按下ctrl-C可以切入到会话交互模式，常用有以下交互命令可以操作：

add_file：增加一个文件到导出目录中，如：

Export>add_file=my_dir:dp02.dmp

 

stop_job：停止数据泵作业，如果设置stop_job=immediate将立即关闭数据泵作业，此时导出服务器进程终止。如果需要重新启动该作业，则需要根据作业名使用attach命令重新开启作业，如：

expdp system/mesHz2@mes attach=system.sys_export_full_01

 

start_job：重新恢复由于某种意外而停止的数据泵作业。

 

kill_job：杀掉客户机进程和数据泵作业的服务器进程。

 

continue_client：退出交互方式，恢复正在运行的数据泵作业，实际的数据泵作业不受影响。

 

exit_client：终止客户机交互式会话，用户可以在当前窗口进行其它操作，但实际的数据泵作业不受影响。

 

parallel：说明当前活动作业的工作者线程数量。

 

status：监视当前作业的状态，参数设置为一个整数值秒数，如status=60，则每60秒会刷新一次作业状态信息。