ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 三个使用数据泵(Data Pump)的小技巧

三个使用数据泵(Data Pump)的小技巧

原创 Linux操作系统 作者:realkid4 时间:2013-08-11 21:52:31 0 删除 编辑

 

数据泵Data Pump是Oracle从10g推出,用于替代传统的exp/imp客户端工具的数据迁移产品。相对于exp/imp工具,数据泵无论是在功能上,还是效率上都有很大提升。本篇主要介绍几个在使用数据泵时候的小技巧,权当记录,供有需要朋友待查。

 

1、环境介绍和构建

 

我们选择Oracle 11gR2进行测试,区别于exp/imp工具,数据泵是一个只能运行在服务端的工具。而且directory对象是需要在数据库中额外建立的。

 

 

[root@bspdev ~]# cd /

[root@bspdev /]# mkdir dumptest

[root@bspdev /]# ls -l | grep dumptest

drwxr-xr-x   2 root     root      4096 Aug 12 03:25 dumptest

[root@bspdev /]# chown -R oracle:oinstall dumptest/

[root@bspdev /]# ls -l | grep dumptest

drwxr-xr-x   2 oracle   oinstall  4096 Aug 12 03:25 dumptest

 

 

在Oracle中创建directory对象。

 

 

SQL> show user

User is "SYS"

 

SQL> create directory dumptest as '/dumptest';

Directory created

 

 

先使用expdp创建出一个dump文件作为实验对象。

 

 

[oracle@bspdev dumptest]$ expdp \"/ as sysdba\" directory=dumptest dumpfile=scott.dmp schemas=scott;

 

Export: Release 11.2.0.1.0 - Production on Mon Aug 12 03:33:36 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

(篇幅原因,有省略……)

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /dumptest/scott.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:34:46

 

 

[oracle@bspdev dumptest]$ ls -l

total 107240

-rw-r--r-- 1 oracle oinstall      2501 Aug 12 03:34 export.log

-rw-r----- 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp

 

 

我们已经获得了一个用于实验的scott.dmp文件。

 

2、使用SQLFILE参数抽取数据对象DDL文本

 

SQLFILE是impdp的一个参数,从官方的定义上,这个参数的含义如下:

 

 

SQLFILE

Write all the SQL DDL to a specified file.

 

 

如果我们在impdp里面设置了sqlfile参数,就意味着我们将dump文件的导入内容并不是直接导入到数据库中,而是以SQL语句形式输出到文本文件里面。这种方式可以用于抽取ddl语句和测试dump文件可用性。我们进行测试如下:

 

 

[oracle@bspdev dumptest]$ impdp \"/ as sysdba\" dumpfile=scott.dmp content=metadata_only sqlfile=scott_ddl.sql directory=dumptest

 

Import: Release 11.2.0.1.0 - Production on Mon Aug 12 03:39:22 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

(篇幅原因,有省略……)

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 03:39:26

 

 

在directory目录中,我们看到了生成的SQL文件。

 

 

[oracle@bspdev dumptest]$ ls -l

total 107320

-rw-r--r-- 1 oracle oinstall      2501 Aug 12 03:34 export.log

-rw-r--r-- 1 oracle oinstall      1290 Aug 12 03:39 import.log

-rw-r--r-- 1 oracle oinstall     72209 Aug 12 03:39 scott_ddl.sql

-rw-r----- 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp

 

其中片段如下:

 

 

-- CONNECT SYS

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:CB685EC52372362B49B7EC43AB0B04BCAF2C71F283C5A558FF8E430F5365;F894844C34402B67'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

 

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT EXECUTE ANY PROCEDURE TO "SCOTT";

 

GRANT CREATE VIEW TO "SCOTT";

 

 

在sql文本里,我们可以找到所有的对应信息和SQL语句。当然,我们也可以针对某些数据表对象进行DDL导出。

 

 

[oracle@bspdev dumptest]$ impdp \"/ as sysdba\" dumpfile=scott.dmp content=metadata_only sqlfile=scott_ddl_single_tb.sql directory=dumptest tables=scott.emp,scott.dept

 

Import: Release 11.2.0.1.0 - Production on Mon Aug 12 03:49:07 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

(篇幅原因,有省略……)

Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at 03:49:09

 

[oracle@bspdev dumptest]$ ls -l

total 107332

-rw-r--r-- 1 oracle oinstall      2501 Aug 12 03:34 export.log

-rw-r--r-- 1 oracle oinstall      1031 Aug 12 03:49 import.log

-rw-r--r-- 1 oracle oinstall      9031 Aug 12 03:49 scott_ddl_single_tb.sql

-rw-r--r-- 1 oracle oinstall     72209 Aug 12 03:39 scott_ddl.sql

-rw-r----- 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp

 

 

在文本中,我们可以方便的找到建表语句。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14 BYTE),

        "LOC" VARCHAR2(13 BYTE)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 

CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10 BYTE),

        "JOB" VARCHAR2(9 BYTE),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

 

 

那么,我们使用sqlfile的场景是什么呢?从笔者的经验上,有两种场景使用sqlfile是非常合适的。

 

首先,是字符集转换不一致问题。数据泵Data Pump因为运行在服务端,所以原则上没有很多字符集问题。不像exp/imp在处理的时候需要进行set/export字符集环境变量。但是,当进行单字节和双字节字符集合导入导出的时候,可能发生数据表长度不能匹配数据的情况。在这种时候,我们需要手工的创建数据表调整字段长度来适应变化。此时,使用SQLFILE来生成DDL语句是很方便的,调整之后就可以直接创建。

 

其次,版本特性差异。我们看到数据表的DDL中,可以看到很多Table Annonation,这些是数据表特性的属性值。在Oracle中,不同版本下,如Enterprise和Standard版本,支持特性是有一些差异的。所以,如果发生源数据库的特性取值在目标数据库中不匹配的现象,就可以借用这个参数进行DDL语句输出,之后调整数据表DDL以适应目标版本。

 

SQLFILE是一种很方便的工具参数。

 

3、Parfile参数文件

 

同exp/imp一样,Data Pump也支持三种工作模式,全库模式(Full)、用户策略(Schema)模式和数据表(Table)模式。

 

各种模式下,组合参数有一定差异。而且,如果进行复杂的参数条件,如加入了query、include和exclude参数,我们调用expdp/impdp的参数列表就很长了。此时,借用一个key-value设置的参数文件就非常方便。

 

官方文件中,对parfile的解释如下:

 

 

PARFILE

Specify parameter file name.

 

 

我们定义一个文本参数文件par.txt。

 

 

[oracle@bspdev dumptest]$ cat par.txt

directory=dumptest

schemas=scott

dumpfile=scott_par.dmp

query=scott.emp:"where 1=0"

 

 

参数以key=value的方式进行保存,注意query部分与我们用命令行时候的差别。在parfile里面,我们不需要关注字符转义。

 

 

[oracle@bspdev dumptest]$ expdp \"/ as sysdba\" parfile=par.txt

 

Export: Release 11.2.0.1.0 - Production on Mon Aug 12 05:31:36 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" parfile=par.txt

(篇幅原因,有省略……)

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /dumptest/scott_par.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:32:05

 

 

如果我们进行数据泵参数比较复杂的时候,可以考虑使用parfile保存一定的参数。

 

4、query参数

 

Tables、Schemas负责定义对象的范围,而include和exclude负责定义数据操作的类型。而query主要负责定义数据表的那些数据(设置条件)会被导入导出。

 

下面是query参数的官方定义。

 

 

QUERY

Predicate clause used to export a subset of a table.

For example, QUERY=employees:"WHERE department_id > 10".

 

 

我们试着将导出的scott数据,导入到一个不存在的用户test里面。注意:Data Pump是会创建对象的。

 

 

[oracle@bspdev dumptest]$ impdp \"/ as sysdba\" directory=dumptest remap_schema=scott:test dumpfile=scott.dmp query=scott.t_com:\"where 1=0\"

 

Import: Release 11.2.0.1.0 - Production on Mon Aug 12 04:07:58 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=dumptest remap_schema=scott:test dumpfile=scott.dmp query=scott.t_com:"where 1=0"

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."T_COM"                              103.3 MB       0 out of 1163088 rows

(篇幅原因,有省略……)

Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 04:08:03

 

 

注意几个问题:

 

首先,如果我们在命令行里面设置参数,就需要注意转义字符的处理。一些如引号之类的对象,需要使用\进行处理。

 

另外,如果我们有一些数据表不想导入,可以在query里面设置上1=0这样的条件。从而实现灵活的处理方案。

 

我们看一下实际效果,证明test下的t_com的确没有数据。

 

 

SQL> select count(*) from scott.t_com;

 

  COUNT(*)

----------

   1163088

 

SQL> select count(*) from test.t_com;

 

  COUNT(*)

----------

         0

 

 

最后,我们思考一个问题,就是我们将scott给remap到test用户上了,那么test用户登录怎么办?使用哪个密码?

 

 

SQL> conn test/tiger@wilson

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as test

 

 

密码和scott一样。

 

5、结论

 

Oracle的Data Pump是我们目前默认使用的数据导入导出工具。了解参数列表,了解工具特性,能够在实际工作中解决很多问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-768245/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7678552