Data Pump(数据抽取)介绍

从10g开始，Oracle提供更高效的Data Pump（即expdp/impdp）来进行数据的导入和导出，老的exp/imp还可以用，但已经不建议使用。注意：expdp/impdp和exp/imp之间互不兼容，也就是说exp导出的文件只能用imp导入，expdp导出的文件只能用impdp导入。

 

Data Pump的组成部分

 

Data Pump有以下三个部分组成：

1. 客户端工具：expdp/impdp 
2. Data Pump API (即DBMS_DATAPUMP)
3. Metadata API（即DMBS_METADATA)

通常情况下，我们都把expdp/impdp等同于Data Pump，但从上面可以知道，实际上它只是Data Pump的一个组成部分，其实真正干活的是两个API，只是它们隐藏在后台，平时很少被注意，但如果出现一些莫名其妙的错误（如internal error等），通常是因为这两个API损坏，跑脚本重新编译它们即可。

 

Data Pump相关的角色

 

默认情况下，用户可以导出/导入自己schema下的数据，但如果要导出/导入其它schema下的数据，必须要把以下两个角色赋予该用户：

• DATAPUMP_EXP_FULL_DATABASE 
• DATAPUMP_IMP_FULL_DATABASE

当然，sys,system账户和dba角色默认拥有以上两个角色。

 

Data Pump数据导入方法

1. 数据文件拷贝：这种是最快的方法，dumpfile里只包含元数据，在操作系统层面拷贝数据文件，相关参数有：TRANSPORT_TABLESPACES,TRANSPORTABLE=ALWAYS 
2. 直接路径加载：这是除了文件拷贝之外最快的方法，除非无法用（比如BFILE），否则都用这种方法
3. 外部表：第1,2种无法用的情况下，才会使用外部表
4. 传统路径加载：只有在以上所有方法都不可用的情况下，才会使用传统路径加载，这种方法性能很差 

 

Data Pump Job

 

当执行expdp/impdp时，其实是起了job执行导出导入工作，一个Data Pump job由以下三部分组成：

1. 主进程(master process）：控制整个job，是整个job的协调者。
2. 主表(master table）：记录dumpfile里数据库对象的元信息，expdp结束时将它写入dumpfile里，impdp开始时读取它，这样才能知道dumpfile里的内容。
3. 工作进程(worker processes）：执行导出导入工作，根据实际情况自动创建多个工作进程并行执行，但不能超过参数PARALLEL定义的个数。

 

监控Job状态

 

在屏幕的输出、logfile里都能看到当前Data Pump Job的运行情况，在数据库里也可以查询视图DBA_DATAPUMP_JOBS,USER_DATAPUMP_JOBS, or DBA_DATAPUMP_SESSIONS。

对于时间比较长的Job，可以在动态视图V$SESSION_LONGOPS查看当前Job完成情况以及预估多久能全部完成，具体字段的意义如下：

USERNAME - job owner
OPNAME - job name
TARGET_DESC - job operation
SOFAR - megabytes transferred thus far during the job
TOTALWORK - estimated number of megabytes in the job
UNITS - megabytes (MB)
MESSAGE - a formatted status message of the form:
'job_name: operation_name : nnn out of mmm MB done'

创建Directory

 

Data Pump不像exp/imp可以在客户端执行，它必须得在服务器端执行，它生成的所有文件都放在服务器端，因此在Oracle里必须得先创建directory对象，下面是一个例子：

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

创建了directory对象之后，还要把读写权限赋给执行Data Pump的用户，如下所示：

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

 

导出模式

 

有以下5种导出模式，它们之间是互斥的，不可以同时使用，注意：有些schemas是不能被导出的，如SYS, ORDSYS, and MDSYS等。

 

Full模式

 

设置Full=y（默认为n），导出全库，例子：

> expdp user_name FULL=y DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir LOGFILE=export.log

Schema模式

 

导出一个或多个Schemas（参数SCHEMAS），默认导出当前用户的schema，只有拥有DATAPUMP_EXP_FULL_DATABASE角色才能导出其它Schemas， 例子：

 

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

 

Table模式

 

导出表、分区以及它们依赖的对象，参数TABLES的语法如下：

TABLES=[schema_name.]table_name[:partition_name] [, ...]

如果schema_name省略，表示导出当前用户schema下的表，

expdp hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=YES

Tablespace模式

 

导出一个或多个Tablespaces，参数TABLESPACES指定要导出的表空间

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp
TABLESPACES=tbs_4, tbs_5, tbs_6

Transpotable Tablespace模式

 

只导出Tablespace的元数据，数据文件可由操作系统层拷贝

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=YES LOGFILE=tts.log

导出过程中的过滤

 

数据过滤

 

参数QUERY在行级别解析数据，其语法如下：

QUERY = [schema.][table_name:] query_clause

 

下面是一个例子：

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"
NOLOGFILE=YES
DIRECTORY=dpump_dir1
DUMPFILE=exp1.dmp

参数SAMPLE指定导出百分比，其语法如下：

SAMPLE=[[schema_name.]table_name:]sample_percent

下面是一个例子：

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=sample.dmp SAMPLE=70

元数据过滤

 

元数据解析采用EXCLUDE，INCLUDE参数，注意：它们俩互斥。

EXCLUDE例子：

expdp FULL=YES DUMPFILE=expfull.dmp EXCLUDE=SCHEMA:"='HR'"
> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_exclude.dmp EXCLUDE=VIEW,
PACKAGE, FUNCTION

INCLUDE例子：

SCHEMAS=HR
DUMPFILE=expinclude.dmp
DIRECTORY=dpump_dir1
LOGFILE=expinclude.log
INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"
INCLUDE=PROCEDURE
INCLUDE=INDEX:"LIKE 'EMP%'"

主要参数说明

 

CONTENT：指定只导出数据、元数据还是全部

DIRECTORY：指定数据库定义的DIRECTORY对象

DUMPFILE：指定dump文件名，如果定义了PARALLEL和FILESIZE，一定要包含通配符%U，如：

> expdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp,
 exp2%U.dmp PARALLEL=3

ESTIMATE_ONLY：如果你只想事先评估下dump文件占用空间大小，可以指定ESTIMATE_ONLY=yes

FILESIZE：指定每个文件大小上限

HELP：如果忘记参数，可以执行expdp help=y 显示帮助信息

JOB_NAME：指定expdp的job名称，一般不用指定，系统会指定默认唯一的JOB_NAME，可以在logfile里查看该Job的名称

LOGFILE：指定日志文件名称，默认为export.log

PARALLEL：默认为1，即不启用并行，对于比较大的导出，建议适当增加并行度，但最大不能超过CPU个数的2倍

PARFILE：指定参数文件名称

REMAP_DATA：假设你从生产库导出敏感信息到测试库上，为了不让这些敏感信息泄露，可以在导出时对这些信息进行修改，从而达到保护敏感信息的作用。

REUSE_DUMPFILE：设置是否重用已有的DUMPFILE

 

 

导入模式

和导出模式一样，以下5种模式也是互斥的。

 

Full模式

 

设置Full=yes即可，如果没有DATAPUMP_IMP_FULL_DATABASE角色，只能导入到自己schema下的数据 

 

Schema模式

 

设置Schema参数，语法如下

SCHEMAS=schema_name [,...]

下面这个例子导入hr数据到hr schema下

> impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp

Table模式

 

设置Table参数，语法如下：

TABLES=[schema_name.]table_name[:partition_name]

如果没有指定schema_name，默认表示导入当前用户的schema下，如：

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs

也可以导入指定的分区：

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp 
TABLES=sh.sales:sales_Q1_2012,sh.sales:sales_Q2_2012

Tablespace模式

设置Tablespace参数，其语法如下：

TABLESPACES=tablespace_name [, ...]

下面是一个例子，要注意的是：这些要导入的tablespace必须已经存在，否则会导入失败。

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLESPACES=tbs_1,tbs_2,tbs_3,tbs_4

Transpotable Tablespace模式

设置Transpotable_tablespace参数，其语法定义如下：

TRANSPORT_TABLESPACES=tablespace_name [, ...]

只导入表空间的元数据，数据文件需有在操作系统层面拷贝至本机服务器下，路径由Transport_datafiles指定，其语法如下：

TRANSPORT_DATAFILES=datafile_name

下面是一个具体例子：

DIRECTORY=dpump_dir1
NETWORK_LINK=source_database_link
TRANSPORT_TABLESPACES=tbs_6
TRANSPORT_FULL_CHECK=NO
TRANSPORT_DATAFILES='user01/data/tbs6.dbf'

导入过程中的过滤

和导出过程中的过滤类似，数据过滤用QUERY和SAMPLE，元数据过滤用EXCLUDE和INCLUDE。

 

主要参数说明

ACCESS_METHOD=[AUTOMATIC | DIRECT_PATH | EXTERNAL_TABLE | CONVENTIONAL]

定义导入方法，强烈建议采用默认设置AUTOMATIC，不要改动。

 

CONTENT=[ALL | DATA_ONLY | METADATA_ONLY]

定义只导入数据、元数据还是都要

 

DIRECTORY=directory_object

指定导入数据文件所在的文件夹

 

DUMPFILE=[directory_object:]file_name [, ...]

指定导入Dump文件名称，可用通配符%U匹配多个Dump文件

 

HELP=YES

impdp help=y 显示帮助信息

 

JOB_NAME=jobname_string

指定Job_name，一般默认即可

 

LOGFILE=[directory_object:]file_name

指定日志文件名

 

MASTER_ONLY=[YES | NO]

指定只导入master table，由于master table包含dumpfile的信息，这样就可以指定dumpfile里包含哪些数据。

 

PARALLEL=integer

指定导入时的并行度

 

PARFILE=[directory_path]file_name

指定参数文件

 

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

导入时对数据进行修改，比如重新生成PK防止和原有的PK冲突等。

 

REMAP_DATAFILE=source_datafile:target_datafile

可以解决异构平台间文件命名规范不同的问题

 

REMAP_SCHEMA=source_schema:target_schema

这个参数很常用，可以让你导入到不同的schema中，如果target_schema不存在，导入时会自动创建，下面是一个例子：

> expdp system SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

> impdp system DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

 

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

可以在导入时重命名表或分区，下面是一个例子：

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp
TABLES=hr.employees REMAP_TABLE=hr.employees:emps 

REMAP_TABLESPACE=source_tablespace:target_tablespace

在导入时修改表空间名，下面是一个例子：

> impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1
  DUMPFILE=employees.dmp

REUSE_DATAFILES=[YES | NO]

是否重用数据文件，默认为NO，一定要谨慎，一旦设为YES，原有同名的数据文件将被覆盖

 

SQLFILE=[directory_object:]file_name

如果指定该参数，则不真正执行导入，而是把导入时所需的DDL SQL写入到SQLFILE里。

 

 

 

expdp的network_link

 

我们知道，expdp默认是导出本地数据库，network_link的作用是导出远程数据库到本地服务器上，其步骤如下：

 

术语说明：

源数据库：远程数据库

目标数据库：本地数据库（即expdp客户端所在的服务器）

 

1. 在目标数据库端添加源数据库的连接字符串至tnsnames.ora：

source_db =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid = orcl)
    )
  )

2. 在目标数据库创建db link：

SQL>create public database link source_db_link connect to system identified by *** using 'test15';

Database link created.

SQL>select instance_name from v$instance@source_db;

INSTANCE_NAME
----------------
orcl

3. 在目标服务器执行expdp：

$ expdp system directory=dump_dir network_link=test15 tables=test.test dumpfile=test.dmp logfile=expdp_test.log

其中network_link等于第2步上创建的db link，dumpfile,logfile放在目标数据库的dump_dir

 

impdp的network_link

上面提到的expdp的network_link是把远程数据库导出至本地的dumpfile，而impdp更强大更方便，它可以直接把远程数据库导入至本地数据库，中间甚至没有dumpfile产生，其语法如下：

$ impdp system directory=dump_dir network_link=test15 tables=test.test logfile=impdp_test.log

上面语句直接把test.test表从源数据库导入至目标数据库，中间不产生dumpfile，到会产生logfile（logfile放在目标数据库端的dump_dir里）

 

 

 

 

当我们起了一个datapump job之后，可以通过v$session_longops查看当前进度。

 

USERNAME - job owner
OPNAME - job name
TARGET_DESC - job operation
SOFAR - megabytes transferred thus far during the job
TOTALWORK - estimated number of megabytes in the job
UNITS - megabytes (MB)
MESSAGE - a formatted status message of the form:
'job_name: operation_name : nnn out of mmm MB done'

 

 

SYS@TEST16>select username,opname,sofar,TOTALWORK,UNITS,message from v$session_longops where opname='SYS_EXPORT_FULL_03';

USERNAME        OPNAME                    SOFAR  TOTALWORK UNITS MESSAGE
--------------- -------------------- ---------- ---------- ----- ------------------------------------------------------------
SYSTEM          SYS_EXPORT_FULL_03         4737      35368 MB    SYS_EXPORT_FULL_03: EXPORT : 4737 out of 35368 MB done

但有时候单单监控是不够的，我们可能还需要修改相应的JOB，这时我们就需要进行datapumo的命令交互模式。

有两种方式可以进入命令交互模式，分别是：

1. 在logging模式下按ctrl+C

2. expdp or impdp attach=SYSTEM.SYS_EXPORT_FULL_03

 

expdp交互模式的命令如下：

 

Activity	Command Used
Add additional dump files.	ADD_FILE
Exit interactive mode and enter logging mode.	CONTINUE_CLIENT
Stop the export client session, but leave the job running.	EXIT_CLIENT
Redefine the default size to be used for any subsequent dump files.	FILESIZE
Display a summary of available commands.	HELP
Detach all currently attached client sessions and terminate the current job.	KILL_JOB
Increase or decrease the number of active worker processes for the current job. This command is valid only in the Enterprise Edition of Oracle Database 11g.	PARALLEL
Restart a stopped job to which you are attached.	START_JOB
Display detailed status for the current job and/or set status interval.	STATUS
Stop the current job for later restart.	STOP_JOB

 

impdp的交互模式命令如下：

 

Activity	Command Used
Exit interactive-command mode.	CONTINUE_CLIENT
Stop the import client session, but leave the current job running.	EXIT_CLIENT
Display a summary of available commands.	HELP
Detach all currently attached client sessions and terminate the current job.	KILL_JOB
Increase or decrease the number of active worker processes for the current job. This command is valid only in Oracle Database Enterprise Edition.	PARALLEL
Restart a stopped job to which you are attached.	START_JOB
Display detailed status for the current job.	STATUS
Stop the current job.	STOP_JOB

 

下面以expdp为例，介绍几个常用命令（如果忘记命令，敲万能的help）。

1. status：查看当前job的状态，如完成的百分比、并行度等，每个worker代表一个并行进程。

 

Export> status

Job: SYS_EXPORT_FULL_03
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 8,357,285,928
  Percent Done: 23
  Current Parallelism: 2
  Job Error Count: 0
  Dump File: /home/oracle/dump/full_%u.dmp
  Dump File: /home/oracle/dump/full_01.dmp
    bytes written: 8,357,294,080
  Dump File: /home/oracle/dump/full_02.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
  Completed Objects: 5,120
  Worker Parallelism: 1

Worker 2 Status:
  Process Name: DW01
  State: EXECUTING
  Object Schema: P95169
  Object Name: GRADE_RCCASE
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  Completed Objects: 3
  Total Objects: 1,866
  Completed Rows: 23,505,613
  Worker Parallelism: 1

2. parallel：动态调整并行度

 

 

Export> parallel=4

3. add_file：增加dumpfile

 

 

Export> ADD_FILE=hr2.dmp, dpump_dir2:hr3.dmp

4. stop_job, kill_job, start_job

 

stop_job只是暂停，之后可以用start_job重新启动，而kill_job直接杀掉，不可恢复

5. continue_client：退出交互模式，进入logging模式；

exit_client: 退出客户端

 

 转：http://blog.csdn.net/dbanote/article/details/9428837