如何从逻辑备份的dumpfile文件里获取DDL脚本?

在数据库的管理工作中，难免会遇到使用Export/Import或者是EXPort Data Pump/IMPort Data Pump工具来执行逻辑备份、恢复的场景。在有些时候，我们可能会对产生的dumpfile的内容感兴趣。本文就这两种工具产生的逻辑备份文件分别描述如何获取数据定义语句(Data Definition Language)的脚本？

1 先来看，如何从传统的Export工具产生的dumpfile里获取DDL语句？

首先，导出HR用户的所有对象：

rac1-> id

uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)

rac1-> pwd

/home/oracle

rac1-> ls -l

total 24

drwxrwxr-x 17 root   root      4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall 15626 Aug 31 14:28 db.rsp

drwxr-x---  3 oracle oinstall  4096 Aug 31 15:03 oradiag_oracle

rac1-> exp hr/hr file=exp_hr.dmp log=exp_hr.log owner=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 16:50:22 2011

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 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user HR

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user HR

About to export HR's objects ...

. exporting database links

. exporting sequence numbers

.....

.....

. exporting statistics

Export terminated successfully without warnings.

rac1-> ls -l

total 23656

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

rac1->

我们已经顺利导出了一份exp_hr.dmp的dumpfile，那么该如何从该文件里获取DDL语句呢？

我们可以利用传统的导入工具Import附带SHOW=y选项来获取：

rac1-> imp hr/hr file=exp_hr.dmp log=show_ddl.log  show=y

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:03:26 2011

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 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

...

...

Import terminated successfully without warnings.

rac1->

rac1-> ls -l

total 23688

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log

rac1-> head -30 show_ddl.log

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"

"CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41155490');"

"COMMIT; END;"

"CREATE TYPE "MY_TEST_TYPE" TIMESTAMP '2011-10-12:09:59:12' OID 'AF115DDE620"

"59E4AE04010ACBF000672'   as object(id number,name varchar2(10));"

"CREATE SEQUENCE "LOCATIONS_SEQ" MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 S"

"TART WITH 3300 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "DEPARTMENTS_SEQ" MINVALUE 1 MAXVALUE 9990 INCREMENT BY 10 "

"START WITH 280 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "EMPLOYEES_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999"

"99999 INCREMENT BY 1 START WITH 207 NOCACHE NOORDER NOCYCLE"

"CREATE CLUSTER "DEPT_EMP_CLUSTER" ("DEPARTMENT_ID" NUMBER(4, 0))  PCTFREE 1"

"0 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 NEXT 1048576 MI"

"NEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"

"SERS""

"CREATE INDEX "IDX_DEPT_EMP_CLUSTER" ON CLUSTER "DEPT_EMP_CLUSTER" PCTFREE 1"

"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"

"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS""

"CREATE TABLE "BIGFILE_TAB" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 P"

"CTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXT"

"ENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIG_T"

rac1->

从上，我们可以看到，我们在IMPORT命令行上带上SHOW=Y的选项时，IMPORT工具并没有真正的执行数据导入，而是生成了一份含有DDL语句的日志文件，我们可以直接编辑该日志文件，以获取我们所需的脚本。

2 那么，对于EXPDP产生的dumpfile，又该如何获取DDL语句呢？

先以EXPDP导出dumpfile：

rac1-> pwd

/u01/app/oracle/mig_dir

rac1-> ls -l

total 0

rac1-> expdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=expdp_hr.log schemas=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 17:16:45 2011

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 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

...

Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:19:48

rac1-> ls -l

total 24248

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

rac1->

该如何从expdp_hr.dmp文件里获取DDL语句呢？IMPDP工具给我们提供了SQLFILE的命令行选项，同样是只获取DDL语句，并未真正的执行数据导入。

rac1-> impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=hr_ddl.sql

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:23:23 2011

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 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

...

...

Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 17:23:47

rac1-> ls -l

total 24360

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql

-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log

rac1-> head -30 hr_ddl.sql

-- CONNECT HR

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/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41161309');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC

CREATE TYPE "HR"."MY_TEST_TYPE"

OID 'AF115DDE62059E4AE04010ACBF000672' as object(id number,name varchar2(10));

/

ALTER TYPE "HR"."MY_TEST_TYPE"

COMPILE SPECIFICATION

PLSQL_OPTIMIZE_LEVEL=  2

PLSQL_CODE_TYPE=  INTERPRETED

PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

/

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

CREATE SEQUENCE  "HR"."LOCATIONS_SEQ"  MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 START WITH 3300 NOCACHE  NOORDER  NOCYCLE ;

rac1->

最后，简单一句话总结：

对于使用传统工具EXPORT导出的dumpfile，我们可以使用IMPORT加上SHOW=Y的选项获取DDL；

对于使用数据泵EXPDP导出的dumpfile，我们可以使用IMPDP加上SQLFILE的选项获取DDL。