Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？ / 四六文摘

QQ群里有人问：如何导出一个用户下的存储过程？
麦苗答：方法有多种，可以使用DBMS_METADATA.GET_DDL包。

使用PL/SQL DEVELOPER工具

-- 下面的SQL语句，如果报错：ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 4994, 最大: 4000)，那么去掉TO_CAHR

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)) ||CHR(10)||'/'

FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE'
;

然后将结果拷贝到Excel中，

打开Excel，复制内容到plsql developer里边，注意粘贴的时候使用右键的“Past from host Language”，否则粘贴后的代码含有双引号：

运行这些脚本脚本即可：

使用SQL*Plus

使用如下的脚本即可导出某个用户下的存储过程代码到/tmp/a.sql文件中：

SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FEEDBACK OFF
SET FEED OFF;
SET ECHO OFF
spool /tmp/a.sql
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)||CHR(10)||'/'
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE';
spool OFF

打开文件后，简单处理一下即可。

总体来说有两种方式来获取，第一，利用系统包DBMS_METADATA包中的GET_DDL函数来获取，第二，利用exp或expdp来获取。

下面来看第一种方式，如何利用系统包DBMS_METADATA包中的GET_DDL函数来获取对象的定义语句。下面是该函数的入参和出参：

SQL> DESCDBMS_METADATA.GET_DDL

PARAMETER TYPE MODE DEFAULT?

----------- -------- ------------

(RESULT) CLOB

OBJECT_TYPE VARCHAR2 IN

NAME VARCHAR2 IN

SCHEMA VARCHAR2 IN Y

VERSION VARCHAR2 IN Y

MODEL VARCHAR2 IN Y

TRANSFORM VARCHAR2 IN Y

其详细参数如下：

l OBJECT_TYPE 需要返回原数据的DDL语句的对象类型

l NAME 对象名称

l SCHEMA 对象所在的SCHEMA，默认为当前用户所在所SCHEMA

l VERSION 对象原数据的版本

l MODEL 原数据的类型默认为ORACLE

l TRANSFORM 默认值为DDL

l RETURNS 对象的原数据默认以CLOB类型返回

一般情况下，只需要给出OBJECT_TYPE、NAME和SCHEMA3个参数即可。

n 查看创建表SQL语句：

SELECTDBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)FROM USER_TABLES U;

n 查看创建索引的SQL语句：

SELECTDBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)FROM USER_INDEXES U;

n 查看创建主键的SQL语句：

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK')FROM DUAL;

n 查看创建外键的SQL语句：

SELECTDBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;

n 查看创建视图（VIEW）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('VIEW','MY_TABLES','SCOTT') FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'VIEW';

SELECT TEXT FROM USER_VIEWS WHEREVIEW_NAME=UPPER('&VIEW_NAME');

n 查看创建存储过程（PROCEDURE）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'PROCEDURE';

n 查看创建触发器（TRIGGER）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('TRIGGER',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'TRIGGER';

n 查看创建函数（FUNCTION）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'FUNCTION';

n 查看创建包（PACKAGE）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('PACKAGE',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'PACKAGE';

n 查看创建序列（SEQUENCE）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('SEQUENCE',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'SEQUENCE';

n 查看创建同义词（SYNONYM）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('SYNONYM',U.OBJECT_NAME)

FROMUSER_OBJECTS U

WHEREOBJECT_TYPE = 'SYNONYM';

n 查看创建表空间（TABLESPACE）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('TABLESPACE',U.TABLESPACE_NAME)

FROMUSER_TABLESPACES U;

n 查看创建角色（ROLE）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROMDBA_ROLES U;

n 查看创建用户（USER）的SQL语句：

SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROMDUAL;

n 得到某个SCHEDULER JOB的创建语句：

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', D.JOB_NAME,D.OWNER)

FROMDBA_SCHEDULER_JOBS D

WHERED.JOB_TYPE = 'STORED_PROCEDURE'

ANDD.STATE = 'SCHEDULED'

ANDD.SCHEDULE_NAME IS NULL;

n 得到一个用户下的所有表、索引、存储过程、函数的DDL语句：

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME)

FROM USER_OBJECTS U

WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION');

如果想去掉表的存储参数（例如，INITIAL、NEXT、FREELISTS等参数），那么可以使用DBMS_METADATA包中的函数SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE)来完成，代码如下所示：

SYS@lhrdb> SELECTDBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

--------------------------------------------------------------------------------

CREATETABLE "SCOTT"."DEPT"

( "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

USING INDEXPCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" ENABLE

) SEGMENTCREATION IMMEDIATE

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS"

SYS@lhrdb> EXECUTEDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SYS@lhrdb> SELECTDBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')

--------------------------------------------------------------------------------

CREATETABLE "SCOTT"."DEPT"

( "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

USING INDEXPCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

TABLESPACE"USERS" ENABLE

) SEGMENTCREATION IMMEDIATE

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

TABLESPACE"USERS"

使用DBMS_METADATA.GET_DDL需要注意以下问题：

（1）DBMS_METADATA.GET_DDL()包内的参数都要大写，否则会报ORA-31600: invalid input value table for parameter OBJECT_TYPE infunction GET_DDL的错误。

（2）是否查的当前用户的DDL语句，若不是则需要加上对象的属主信息即SCHEMA参数。

（3）若在SQL*Plus中显示不全，则需要set long 9999。

（4）对于DBMS_METADATA.GET_DDL包，可以在PLSQL Developer工具中运行，也可以在SQL*Plus中运行。

如果要导出SCOTT用户下的所有定义，那么在SQL*Plus中代码如下所示：

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF;

SET ECHO OFF

SPOOL /tmp/schema_scott.sql

SELECT CASE

WHENU.OBJECT_TYPE IN

('PROCEDURE', 'FUNCTION' ， 'PACKAGE', 'TRIGGER') THEN

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||

CHR(10) || '/'

ELSE

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||

CHR(10) || ';'

END ASSCOTT_DDL

FROMDBA_OBJECTS U

WHEREU.OBJECT_TYPE IN

('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION' ， 'PACKAGE', 'TRIGGER')

AND U.OWNER='SCOTT';

SPOOL OFF;

则可以导出SCOTT用户下所有的DDL语句到/tmp/schema_scott.sql文件中。

如果在PLSQL Developer工具中运行，那么可以单独运行如下的SQL语句：

SELECT CASE

WHENU.OBJECT_TYPE IN

('PROCEDURE', 'FUNCTION' ， 'PACKAGE', 'TRIGGER') THEN

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||

CHR(10) || '/'

ELSE

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||

CHR(10) || ';'

END ASSCOTT_DDL

FROMDBA_OBJECTS U

WHEREU.OBJECT_TYPE IN

('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION' ， 'PACKAGE', 'TRIGGER')

AND U.OWNER='SCOTT';

然后选择整列，右键选择“Copy to Excel”，就可以将数据导出到Excel文件中，接着，将Excel中的数据复制到PLSQL Developer工具的“SQL Window”中皆可。需要注意的是，最后复制到“SQL Window”中的时候，需要选择右键的“Past from host Language”，否则粘贴的代码含有双引号，需要做特殊处理，比较麻烦。

可以使用如下的SQL脚本生成某个用户下的所有对象的DDL语句：

sqlplus<

set long 100000

set head off

set echo off

set pagesize 0

set verify off

set feedback off

spool schema.out

select dbms_metadata.get_ddl(object_type,object_name, owner)

from

(

--ConvertDBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:

select

owner,

--Java object names may need to be converted with DBMS_JAVA.LONGNAME.

--That code is not included since many database don't have Javainstalled.

object_name,

decode(object_type,

'DATABASE LINK', 'DB_LINK',

'JOB', 'PROCOBJ',

'RULE SET', 'PROCOBJ',

'RULE', 'PROCOBJ',

'EVALUATION CONTEXT', 'PROCOBJ',

'PACKAGE', 'PACKAGE_SPEC',

'PACKAGE BODY', 'PACKAGE_BODY',

'TYPE', 'TYPE_SPEC',

'TYPE BODY', 'TYPE_BODY',

'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',

'QUEUE', 'AQ_QUEUE',

'JAVA CLASS', 'JAVA_CLASS',

'JAVA TYPE', 'JAVA_TYPE',

'JAVA SOURCE', 'JAVA_SOURCE',

'JAVA RESOURCE', 'JAVA_RESOURCE',

object_type

)object_type

fromdba_objects

whereowner in ('LHR')

--These objects are included with other object types.

andobject_type not in ('INDEX PARTITION','INDEX SUBPARTITION',

'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLESUBPARTITION','PROGRAM')

--Ignore system-generated types that support collection processing.

andnot (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')

--Exclude nested tables, their DDL is part of their parent table.

and(owner, object_name) not in (select owner, table_name from dba_nested_tables)

--Exlclude overflow segments, their DDL is part of their parent table.

and(owner, object_name) not in (select owner, table_name from dba_tables whereiot_type = 'IOT_OVERFLOW')

)

order by owner, object_type, object_name;

spool off

quit

EOF

cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

下面介绍第二种导出元数据的方法，就是采用exp或expdp命令。数据泵工具（impdp）提供了SQLFILE的命令行选项，只获取DDL语句，并未真正地执行数据导入。另外，若单纯为了导出DDL语句则可以在使用expdp导出的时候使用CONTENT=METADATA_ONLY和EXCLUDE=STATISTICS选项，这样导出的DMP文件比较小。如下所示：

expdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIRDUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLYSCHEMAS=SCOTT EXCLUDE=STATISTICS

impdp \'/ ASSYSDBA\' DIRECTORY=DATA_PUMP_DIRDUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql

查看expddl_lhr.sql文件即可获取DDL语句。整个示例如下所示：

[ZFZHLHRDB1:oracle]:/oracle>expdp \'/ AS SYSDBA\'directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp

Export: Release 11.2.0.4.0 - Production on Wed Aug3 15:14:55 2016

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.4.0 - 64bit Production

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

Data Mining and Real Application Testing options

Starting"SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA"directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmplogfile=exp_exptest.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 256 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object typeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported"SCOTT"."DEPT" 5.929 KB 4 rows

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

. . exported"SCOTT"."SALGRADE" 5.859 KB 5 rows

. . exported"SCOTT"."TEST" 5.007 KB 1 rows

. . exported"SCOTT"."BONUS" 0 KB 0 rows

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

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

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp

Job"SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at WedAug 3 15:15:16 2016 elapsed 0 00:00:20

[ZFZHLHRDB1:oracle]:/oracle>impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

Import: Release 11.2.0.4.0 - Production on Wed Aug3 15:16:06 2016

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.4.0 - 64bit Production

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

Data Mining and Real Application Testing options

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

Starting"SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA"directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.logsqlfile=exptest.sql

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job"SYS"."SYS_SQL_FILE_FULL_01" successfully completed at WedAug 3 15:16:09 2016 elapsed 0 00:00:02

[ZFZHLHRDB1:oracle]:/oracle>cd/oracle/app/oracle/admin/lhrdb/dpdump/

[ZFZHLHRDB1:oracle]:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql

-- CONNECT SYS

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

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

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

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

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

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

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

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

DEFAULTTABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

PASSWORD EXPIRE

ACCOUNTLOCK;

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANTUNLIMITED TABLESPACE TO "SCOTT";

--new object type path: SCHEMA_EXPORT/ROLE_GRANT

GRANT "CONNECT" TO"SCOTT";

GRANT "RESOURCE" TO"SCOTT";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

ALTER USER"SCOTT" DEFAULT ROLE ALL;

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

-- CONNECT SCOTT

BEGIN

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

COMMIT;

END;

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

-- CONNECT SYS

CREATETABLE "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 MINEXTENTS1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_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)

) SEGMENTCREATION IMMEDIATE

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" ;

CREATE TABLE "SCOTT"."BONUS"

( "ENAME" VARCHAR2(10 BYTE),

"JOB" VARCHAR2(9 BYTE),

"SAL" NUMBER,

"COMM" NUMBER

) SEGMENTCREATION DEFERRED

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

TABLESPACE"USERS" ;

CREATE TABLE "SCOTT"."SALGRADE"

( "GRADE" NUMBER,

"LOSAL" NUMBER,

"HISAL" NUMBER

) SEGMENT CREATION IMMEDIATE

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" ;

CREATE TABLE "SCOTT"."TEST"

( "DUMMY" VARCHAR2(1 BYTE)

) SEGMENTCREATION IMMEDIATE

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESSLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" ;

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

-- CONNECT SCOTT

CREATE UNIQUE INDEX"SCOTT"."PK_DEPT" ON "SCOTT"."DEPT"("DEPTNO")

PCTFREE 10INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" PARALLEL 1 ;

ALTER INDEX"SCOTT"."PK_DEPT" NOPARALLEL;

CREATE UNIQUE INDEX"SCOTT"."PK_EMP" ON "SCOTT"."EMP"("EMPNO")

PCTFREE 10INITRANS 2 MAXTRANS 255

STORAGE(INITIAL65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE"USERS" PARALLEL 1 ;

ALTER INDEX"SCOTT"."PK_EMP" NOPARALLEL;

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

-- CONNECT SYS

ALTER TABLE "SCOTT"."DEPT" ADDCONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

USING INDEX"SCOTT"."PK_DEPT" ENABLE;

ALTER TABLE "SCOTT"."EMP" ADDCONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

USING INDEX"SCOTT"."PK_EMP" ENABLE;

-- new object type path:SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

DECLARE I_N VARCHAR2(60);

I_OVARCHAR2(60);

NVVARCHAR2(1);

cDBMS_METADATA.T_VAR_COLL;

dfvarchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

stmtvarchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS"(type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1)VALUES (''I'',6,:1,:2,:3,:4,:5,

:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';

BEGIN

DELETE FROM"SYS"."IMPDP_STATS";

i_n :='PK_DEPT';

i_o :='SCOTT';

EXECUTEIMMEDIATE stmt USING 2,I_N,NV,NV,I_O,4,1,4,1,1,1,0,4,NV,NV,TO_DATE('2016-07-0722:00:11',df),NV;

DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' ||i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');

DELETE FROM"SYS"."IMPDP_STATS";

END;

《《《《。。。。。。。。篇幅原因，有省略,剩下的都是统计信息，生成sqlfile的时候也可以不用生成。。。。。。。。》》》》

imp工具使用SHOW=Y LOG=GET_DDL.sql的方式，可以看到清晰的DDL脚本，同时也不会真正的执行数据导入。另外，若单纯为了导出DDL语句则可以在使用exp导出的时候使用ROWS=N选项，这样导出的DMP文件比较小。如下所示：

exp \'/ ASSYSDBA\' TABLES=SCOTT.EMP FILE=/tmp/exp_ddl_lhr_01.dmp LOG=/tmp/exp_table.log BUFFER=41943040 ROWS=N COMPRESS=N

imp \'/ AS SYSDBA\' FILE=/tmp/exp_ddl_lhr_01.dmpSHOW=Y LOG=/tmp/get_ddl.sql BUFFER=20480000 FULL=Y

查看get_ddl.sql文件即可获取DDL语句。不过对于exp生成的DDL语句不能直接使用，需要使用SHELL脚本做相应的处理后才能使用。整个示例如下所示：

[ZFZHLHRDB1:oracle]:/oracle>exp \'/ AS SYSDBA\' tables=scott.emp file=/tmp/exp_ddl_lhr_01.dmp log=/tmp/exp_table.log buffer=41943040 rows=n compress=n

Export: Release 11.2.0.4.0 - Production on Tue Aug2 15:42:11 2016

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.4.0 - 64bit Production

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

Data Mining and Real Application Tes

Export done in ZHS16GBK character set and AL16UTF16NCHAR character set

Note: table data (rows) will not be exported

About to export specified tables via ConventionalPath ...

Current user changed to SCOTT

. . exporting table EMP

Export terminated successfully without warnings.

[ZFZHLHRDB1:oracle]:/oracle>imp \'/ AS SYSDBA\'file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y

Import: Release 11.2.0.4.0 - Production on Tue Aug2 15:42:44 2016

Connected to: Oracle Database 11g EnterpriseEdition Release 11.2.0.4.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 viaconventional path

import done in ZHS16GBK character set and AL16UTF16NCHAR character set

. importing SYS's objects into SYS

. importing SCOTT's objects into SCOTT

"ALTERSESSION SET CURRENT_SCHEMA= "SCOTT""

"CREATETABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME"VARCHAR2(10), "JOB" VARCH"

"AR2(9), "MGR" NUMBER(4, 0),"HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM"NUM"

"BER(7,2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"

"S 255STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST "

"GROUPS1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"

"CREATEUNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX"

"TRANS255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"

"ISTGROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"

"ALTERSESSION SET CURRENT_SCHEMA= "SCOTT""

"ALTERTABLE "EMP" ADD CONSTRAINT"PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"

"XPCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"

"EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT) TABLESPACE "US"

"ERS" LOGGING ENABLE "

"ALTERTABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY("DEPTNO") REFEREN"

"CES"DEPT" ("DEPTNO") ENABLE NOVALIDATE"

"ALTERTABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""

Import terminated successfully without warnings.

[ZFZHLHRDB1:oracle]:/oracle>

由于格式比较混乱，直接运行会报错，建荣的书中给了一段代码来格式化：

[ZFZHLHRDB1:oracle]:/tmp>more /tmp/get_ddl.sql