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

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

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

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

 

01

rac1-> id

02

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

04

05	rac1-> pwd

06

07	/home/oracle

08

09	rac1-> ls -l

10

11

total 24

12

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

14

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

16

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

18

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

20

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

22

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

24

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

26

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

28

29	Data Mining and Real Application Tes

30

31	Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

32

33	. exporting pre-schema procedural objects and actions

34

35	. exporting foreign function library names for user HR

36

37	. exporting PUBLIC type synonyms

38

39	. exporting private type synonyms

40

41	. exporting object type definitions for user HR

42

43	About to export HR's objects ...

44

45	. exporting database links

46

47	. exporting sequence numbers

48

49

.....

50

51

.....

52

53	. exporting statistics

54

55	Export terminated successfully without warnings.

56

57	rac1-> ls -l

58

59	total 23656

60

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

62

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

64

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

66

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

68

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

70

71

rac1->

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

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

view source

print ?

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

002

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

004

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

006

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

008

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

010

011	Data Mining and Real Application Tes

012

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

014

015	import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

016

017	. importing HR's objects into HR

018

019

"BEGIN  "

020

021

...

022

023

...

024

025	Import terminated successfully without warnings.

026

027

rac1->

028

029	rac1-> ls -l

030

031	total 23688

032

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

034

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

036

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

038

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

040

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

042

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

044

045	rac1-> head -30 show_ddl.log

046

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

048

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

050

051	Data Mining and Real Application Tes

052

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

054

055	import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

056

057	. importing HR's objects into HR

058

059

"BEGIN  "

060

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

062

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

064

065	"COMMIT; END;"

066

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

068

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

070

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

072

073	"TART WITH 3300 NOCACHE NOORDER NOCYCLE"

074

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

076

077	"START WITH 280 NOCACHE NOORDER NOCYCLE"

078

079	"CREATE SEQUENCE "EMPLOYEES_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999"

080

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

082

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

084

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

086

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

088

089

"SERS""

090

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

092

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

094

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

096

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

098

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

100

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

102

103

rac1->

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

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

先以EXPDP导出dumpfile：

view source

print ?

01	rac1-> pwd

02

03	/u01/app/oracle/mig_dir

04

05	rac1-> ls -l

06

07

total 0

08

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

10

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

12

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

14

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

16

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

18

19	Data Mining and Real Application Testing options

20

21

...

22

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

24

25	rac1-> ls -l

26

27	total 24248

28

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

30

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

32

33

rac1->

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

view source

print ?

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

02

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

04

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

06

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

08

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

10

11	Data Mining and Real Application Testing options

12

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

14

15

...

16

17

...

18

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

20

21	rac1-> ls -l

22

23	total 24360

24

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

26

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

28

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

30

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

32

33	rac1-> head -30 hr_ddl.sql

34

35	-- CONNECT HR

36

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

38

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

40

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

42

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

44

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

46

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

48

49	-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

50

51

BEGIN

52

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

54

55

COMMIT;

56

57

END;

58

59

/

60

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

62

63	CREATE TYPE "HR"."MY_TEST_TYPE"

64

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

66

67

/

68

69	ALTER TYPE "HR"."MY_TEST_TYPE"

70

71	COMPILE SPECIFICATION

72

73	PLSQL_OPTIMIZE_LEVEL=  2

74

75	PLSQL_CODE_TYPE=  INTERPRETED

76

77	PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

78

79

/

80

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

82

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

84

85

rac1->

最后，简单一句话总结：

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

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