Oracle 使用数据泵进行数据卸载

Oracle 使用数据泵进行数据卸载

从Oracle 10g开始，Oracle引入了另外一个特性数据泵（DataPump），通过数据泵可以快速地对数据库进行数据卸载和加载。本文将主要介绍数据泵对于数据卸载的使用。

1. 使用数据泵外部表卸载数据

数据泵同时引入了一种通过外部表进行数据卸载的方式，先来了解一下这种方式。使用数据泵卸载数据之前首先需要一个DIRECTORY，这可以自行创建也可以使用已有的缺省目录：

SQL> select * from dba_directories 2 where DIRECTORY_NAME='DATA_PUMP_DIR';OWNER DIRECTORY_NAME DIRECTORY_PATH----- ------------------------------ -----------------------------------SYS DATA_PUMP_DIR /opt/oracle/admin/danaly/dpdump/

然后可以通过类似以下语句对数据表进行数据泵格式的卸载，主要的变化是通过TYPE参数指定卸载数据驱动为ORACLE_DATAPUMP：

SQL> CREATE TABLE dp_all_objects 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE oracle_datapump 5 DEFAULT DIRECTORY data_pump_dir 6 LOCATION ( 'DP_ALL_OBJECTS.DMP' ) 7 ) 8 AS 9 SELECT * 10 FROM all_objects;Table created.

再来检查相应的目录，可以看到生成了两个相关文件，一个是日志文件，一个则是卸载的数据文件，由于外部文件由数据库调用直接写出，所以新的外部表文件又被称为可写外部表（writeable external tables），这里的可写仅指创建时可以写出，对于外部表内容仍然是只读的：

[oracle@danaly dpdump]$ cd /opt/oracle/admin/danaly/dpdump/[oracle@danaly dpdump]$ ls -ltotal 4852-rw-r--r-- 1 oracle dba 41 Mar 19 09:37 DP_ALL_OBJECTS_13768.log-rw-r----- 1 oracle dba 4943872 Mar 19 09:37 DP_ALL_OBJECTS.DMP

日志文件后面增加的数字是数据库Session的进程号：

SQL> SELECT spid 2 FROM v$process 3 WHERE addr = (SELECT paddr 4 FROM v$session 5 WHERE SID = (SELECT SID 6 FROM v$mystat 7 WHERE ROWNUM < 2));SPID------------13768

数据泵的转储文件是按照XML格式组织的，简要地看一下这个文件的内容，内容包含了数据字符集，表列字段列表等信息：

[oracle@danaly dpdump]$ strings DP_ALL_OBJECTS.DMP |head -30Linuxi386/Linux-2.0.34-8.1.0ZHS16GBKLBB EMB GHC JWD SD EBE WMF DDG JG SJH SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT10.02.00.01.00T 1 0 3 0 ZHS16GBK AL16UTF16 +08:00 SYS DP_ALL_OBJECTS 1 0 OWNER 1 30 0 0 852 1 30

接下来可以获得这个外部表的完整创建语句：

SQL> set long 12000SQL> set pagesize 99SQL> select dbms_metadata.get_ddl('TABLE','DP_ALL_OBJECTS') from dual;DBMS_METADATA.GET_DDL('TABLE','DP_ALL_OBJECTS')------------------------------------------------------------------------- CREATE TABLE "SYS"."DP_ALL_OBJECTS" ( "OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(30), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "DATA_PUMP_DIR" LOCATION ( 'DP_ALL_OBJECTS.DMP' ))

这样这个转储的文件在其他服务器上也就可以通过外部表的创建进行快速访问和读取了，这是极为方便的。把这个文件转移到Windows平台上，同样使用缺省的DATA_PUMP_DIR路径：

SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';OWNER DIRECTORY_NAME DIRECTORY_PATH----- ------------------------------ ------------------------------SYS DATA_PUMP_DIR C:\oracle\admin\eygle\dpdump\

利用前面的脚本创建外部表，然后可以看到，这些数据可以毫无障碍地被查询：

SQL> select count(*) from dp_all_objects; COUNT(*)---------- 50408

通过数据泵的外部表卸载，现在可以很容易地进行数据转储、转移和访问。在卸载数据时有一点需要注意的是，外部文件不能存在，否则会出现类似如下错误：

ERROR at line 1:ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-11012: file DP_ALL_OBJECTS.DMP in /opt/oracle/admin/danaly/dpdump/ already existsORA-06512: at "SYS.ORACLE_DATAPUMP", line 19

2.通过并行处理提高性能

通过数据泵外部表来卸载数据，还可以支持并行，通过指定多个卸载文件，Oracle会自动启用并行处理，加快数据卸载：

SQL> CREATE TABLE dp_all_objects 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE ORACLE_DATAPUMP 5 DEFAULT DIRECTORY DATA_PUMP_DIR 6 LOCATION ( 'DP_ALL_OBJECTS1.DMP', 'DP_ALL_OBJECTS2.DMP', 'DP_ALL_OBJECTS3.DMP' ,'DP_ALL_OBJECTS4.DMP') 7 ) 8 PARALLEL 4 9 AS 10 SELECT * 11 FROM all_objects;Table created.

检查一下生成的日志文件可以发现一共生成了4个DMP文件，同时还有5个日志文件，1个是主进程日志，4个是并行进程日志：

SQL> ! ls -ltotal 4948-rw-r----- 1 oracle dba 1249280 Mar 18 09:40 DP_ALL_OBJECTS1.DMP-rw-r----- 1 oracle dba 1245184 Mar 18 09:40 DP_ALL_OBJECTS2.DMP-rw-r--r-- 1 oracle dba 41 Mar 18 09:40 DP_ALL_OBJECTS_32689.log-rw-r--r-- 1 oracle dba 41 Mar 18 09:40 DP_ALL_OBJECTS_32715.log-rw-r--r-- 1 oracle dba 41 Mar 18 09:40 DP_ALL_OBJECTS_32717.log-rw-r--r-- 1 oracle dba 41 Mar 18 09:40 DP_ALL_OBJECTS_32719.log-rw-r--r-- 1 oracle dba 41 Mar 18 09:40 DP_ALL_OBJECTS_32721.log-rw-r----- 1 oracle dba 1249280 Mar 18 09:40 DP_ALL_OBJECTS3.DMP-rw-r----- 1 oracle dba 1249280 Mar 18 09:40 DP_ALL_OBJECTS4.DMP

可以看到，Oracle使用了并行处理，转储的数据和现有数据是一致的：

SQL> select count(*) from dp_all_objects; COUNT(*)---------- 50408SQL> select count(*) from all_objects; COUNT(*)---------- 50408

也可以简单地修改外部表的定义，访问单个文件：

SQL> ALTER TABLE dp_all_objects LOCATION ( 'DP_ALL_OBJECTS1.DMP' );Table altered.SQL> select count(*) from dp_all_objects; COUNT(*)---------- 12602