Oracle数据库导出存储过程、函数、包、触发器定义及表结构与索引创建语句方法指南

时间：11-01 神话故事提交错误

今天给各位分享Oracle数据库导出存储过程、函数、包、触发器定义及表结构与索引创建语句方法指南的知识，其中也会对进行解释，如果能碰巧解决你现在面临的问题，别忘了关注本站，现在开始吧！

麦苗回答：方法有很多，可以使用DBMS_METADATA.GET_DDL包。

使用PL/SQL DEVELOPER 工具

-- 下面的SQL语句，如果报错：ORA-22835: buffer is toosmall for CLOB to CHAR conversion or BLOB to RAW conversion (actual : 4994, maximum : 4000),则去掉TO_CAHR

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="过程"

;

然后将结果复制到Excel中，

打开Excel，将内容复制到plsql Developer中，粘贴时一定要使用右键“从宿主语言粘贴”，否则粘贴的代码包含双引号：

只需运行这些脚本：

使用SQL*Plus

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

设置页面大小0

将修剪线轴设置为打开

设置线大小10000

买入90000

设置反馈关闭

设置关闭进给；

设置回声关闭

假脱机/tmp/a.sql

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="过程";

阀芯关闭

打开文件后，只需对其进行处理即可。

一般来说，有两种获取方式。首先使用系统包DBMS_METADATA包中的GET_DDL函数获取。其次，使用exp或expdp来获取。

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

SQLDESC DBMS_METADATA.GET_DDL

参数类型模式默认？

（结果）CLOB

OBJECT_TYPE VARCHAR2 IN

将VARCHAR2 命名为

Y 中的架构VARCHAR2

Y 中的版本VARCHAR2

Y 中的VARCHAR2 模型

将VARCHAR2 转换为Y

其详细参数如下：

lOBJECT_TYPE 需要返回原始数据的DDL语句的对象类型

lNAME 对象名称

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

lVERSION对象原始数据版本

lMODEL原始数据类型默认为ORACLE

lTRANSFORM默认值为DDL

lRETURNS对象的原始数据默认以CLOB类型返回。

一般情况下，只需要给出OBJECT_TYPE、NAME和SCHEMA这三个参数。

n查看创建表的SQL语句：

SELECT DBMS_METADATA.GET_DDL("TABLE","DEPT","SCOTT") FROM DUAL;

从USER_TABLES U 中选择DBMS_METADATA.GET_DDL("TABLE",U.TABLE_NAME);

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

SELECT DBMS_METADATA.GET_DDL("INDEX","PK_DEPT","SCOTT") FROM DUAL;

从USER_INDEXES U 中选择DBMS_METADATA.GET_DDL("INDEX",U.INDEX_NAME);

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

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

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

SELECT DBMS_METADATA.GET_DDL("REF_CONSTRAINT","EMP_FK_DEPT") FROM DUAL;

nView创建视图（VIEW）的SQL语句：

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="查看";

从USER_VIEWS 中选择文本，其中VIEW_NAME=UPPER("VIEW_NAME");

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="过程";

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="TRIGGER";

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="函数";

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="包";

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="序列";

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

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

来自USER_OBJECTS U

WHERE OBJECT_TYPE="同义词";

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

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

来自USER_TABLESPACES U；

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

SELECT DBMS_METADATA.GET_DDL("ROLE", U.ROLE) FROM DBA_ROLES U;

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

SELECT DBMS_METADATA.GET_DDL("USER","SYS") FROM DUAL;

n获取SCHEDULER JOB的创建语句：

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

来自DBA_SCHEDULER_JOBS D

WHERE D.JOB_TYPE="STORED_PROCEDURE"

AND D.STATE="已安排"

并且D.SCHEDULE_NAME 为NULL；

n获取某个用户下所有表、索引、存储过程、函数的DDL语句：

选择DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME)

来自USER_OBJECTS U

WHERE U.OBJECT_TYPE IN ("表", "索引", "过程", "函数");

如果要删除表的存储参数（例如INITIAL、NEXT、FREELISTS等参数），可以使用DBMS_METADATA包中的函数SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,"STORAGE",FALSE)来完成。代码如下：

SYS@lhrdbSELECT DBMS_METADATA.GET_DDL("TABLE","DEPT","SCOTT") FROM DUAL;

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

创建表"SCOTT"."DEPT"

("部门号"编号(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

约束“PK_DEPT”主键（“DEPTNO”）

使用索引PCTFREE 10 INITRANS 2 MAXTRANS 255 计算统计数据

存储（初始65536 下一步1048576 最小扩展1 最大扩展2147483645

PCTINCREASE 0 个自由列表1 个自由列表组1

BUFFER_POOL 默认FLASH_CACHE 默认CELL_FLASH_CACHE 默认）

表空间“用户”启用

) 立即创建段

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

无压缩日志记录

存储（初始65536 下一步1048576 最小扩展1 最大扩展2147483645

PCTINCREASE 0 自由列表1 自由列表组1

BUFFER_POOL 默认FLASH_CACHE 默认CELL_FLASH_CACHE 默认）

表空间“用户”

SYS@lhrdbEXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,"STORAGE",FALSE);

PL/SQL 过程成功完成。

SYS@lhrdbSELECT DBMS_METADATA.GET_DDL("TABLE","DEPT","SCOTT") FROM DUAL;

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

创建表"SCOTT"."DEPT"

("部门号"编号(2,0),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13),

约束“PK_DEPT”主键（“DEPTNO”）

使用索引PCTFREE 10 INITRANS 2 MAXTRANS 255 计算统计数据

表空间“用户”启用

) 立即创建段

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

无压缩日志记录

表空间“用户”

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

（1）DBMS_METADATA.GET_DDL()包中的所有参数必须大写，否则会报错ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL。

(2)是否检查过当前用户的DDL语句。如果没有，则需要添加该对象的所有者信息，即SCHEMA参数。

(3)如果在SQL*Plus中显示不完整，需要设置long 9999。

(4) 对于DBMS_METADATA.GET_DDL包，可以在PLSQL Developer工具或SQL*Plus中运行。

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

设置页面大小0

将修剪线轴设置为打开

设置线大小10000

买入90000

设置反馈关闭

设置关闭进给；

设置回声关闭

SPOOL /tmp/schema_scott.sql

选择案例

当U.OBJECT_TYPE 输入时

("程序", "功能", "包", "触发") 然后

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

CHR(10) || "/"

别的

DBMS_METADATA.GET_DDL（U.OBJECT_TYPE，U.OBJECT_NAME，U.OWNER）||

CHR(10) || ";"

结束为SCOTT_DDL

来自DBA_OBJECTS U

U.OBJECT_TYPE 所在位置

（“表”、“索引”、“过程”、“功能”、“包”、“触发器”）

AND U.OWNER="斯科特";

阀芯关闭；

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

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

选择案例

当U.OBJECT_TYPE 输入时

("程序", "功能", "包", "触发") 然后

DBMS_METADATA.GET_DDL（U.OBJECT_TYPE，U.OBJECT_NAME，U.OWNER）||

CHR(10) || "/"

别的

DBMS_METADATA.GET_DDL（U.OBJECT_TYPE，U.OBJECT_NAME，U.OWNER）||

CHR(10) || ";"

结束为SCOTT_DDL

来自DBA_OBJECTS U

U.OBJECT_TYPE 所在位置

（“表”、“索引”、“过程”、“功能”、“包”、“触发器”）

AND U.OWNER="斯科特";

然后选择整列，右键单击并选择“复制到Excel”，将数据导出到Excel 文件。然后，将Excel中的数据复制到PLSQL Developer工具的“SQL窗口”中。需要注意的是，最后复制到“SQL Window”时，需要右键“从宿主语言粘贴”，否则粘贴的代码包含双引号，需要特殊处理，比较麻烦。

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

sqlplus

设置多头100000

离开

设置回声关闭

设置页面大小0

设置验证关闭

关闭反馈

spoolschema.out

选择dbms_metadata.get_ddl(对象类型，对象名称，所有者)

从

(

--将DBA_OBJECTS.OBJECT_TYPE转换为DBMS_METADATA对象类型：

选择

所有者，

--Java对象名可能需要用DBMS_JAVA.LONGNAME进行转换。

--该代码不包括在内，因为许多数据库没有安装Java。

对象名称，

解码（对象类型，

"数据库链接"，"DB_LINK"，

"工作"，"PROCOBJ"，

"规则集"，"PROCOBJ"，

"规则"，"PROCOBJ"，

"评估上下文"，"PROCOBJ"，

"包装"，"包装_规格"，

"包体"，"PACKAGE_BODY"，

"类型"，"TYPE_SPEC"，

"类型主体"，"TYPE_BODY"，

"物化视图"，"物化视图"，

"队列"，"AQ_QUEUE"，

"JAVA 类", "JAVA_CLASS",

"JAVA 类型", "JAVA_TYPE",

"JAVA 源", "JAVA_SOURCE",

"JAVA 资源", "JAVA_RESOURCE",

对象类型

) 对象类型

来自dba_objects

所有者在("LHR")

--这些对象包含在其他对象类型中。

并且object_type 不在("INDEX PARTITION","INDEX SUBPARTITION",

"LOB"、"LOB 分区"、"LOB 子分区"、"表分区"、"表子分区"、"程序")

--忽略系统生成的支持集合处理的类型。

而不是（object_type="TYPE" 且object_name 如"SYS_PLSQL_%"）

--排除嵌套表，它们的DDL 是其父表的一部分。

并且(owner, object_name) 不在(select Owner, table_name from dba_nested_tables)

--排除溢出段，它们的DDL是它们父表的一部分。

并且(owner, object_name) 不在(select Owner, table_name from dba_tables where iot_type="IOT_OVERFLOW")

）

按所有者、对象类型、对象名称排序；

脱机

辞职

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_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLY SCHEMAS=SCOTT EXCLUDE=STATISTICS

impdp "/AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql

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

[ZFZ

HLHRDB1: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 Aug 3 15:14:55 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition 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.dmp logfile=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 type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_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 Wed Aug 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 Aug 3 15:16:06 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition 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.log sqlfile=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 type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Aug 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 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:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT UNLIMITED 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 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" ; CREATE TABLE "SCOTT"."BONUS" ( "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "SAL" NUMBER, "COMM" NUMBER ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."SALGRADE" ( "GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER ) 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"."TEST" ( "DUMMY" VARCHAR2(1 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" ; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT SCOTT CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 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" PARALLEL 1 ; ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 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" PARALLEL 1 ;

ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYS ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX "SCOTT"."PK_DEPT" ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "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_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := "YYYY-MM-DD:HH24:MI:SS"; stmt varchar2(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"; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,4,1,4,1,1,1,0,4,NV,NV,TO_DATE("2016-07-07 22: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 "/ AS SYSDBA" 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.dmp SHOW=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 Aug 2 15:42:11 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition 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 AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... 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 Aug 2 15:42:44 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition 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 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS"s objects into SYS . importing SCOTT"s objects into SCOTT "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "CREATE TABLE "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 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST " "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS" "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE" "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US" "ERS" LOGGING ENABLE " "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"" Import terminated successfully without warnings. [ZFZHLHRDB1:oracle]:/oracle> 由于格式比较混乱，直接运行会报错，建荣的书中给了一段代码来格式化： [ZFZHLHRDB1:oracle]:/tmp>more /tmp/get_ddl.sql Connected to: Oracle Database 11g Enterprise Edition 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 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS"s objects into SYS . importing SCOTT"s objects into SCOTT "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "CREATE TABLE "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 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST " "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS" "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" "ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE" "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US" "ERS" LOGGING ENABLE " "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"" Import terminated successfully without warnings. [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gettabddl.sh awk " / "BEGIN / { N=1; } / "CREATE / { N=1; } / "CREATE INDEX/ { N=1; } / "CREATE UNIQUE INDEX/ { N=1; } / "ALTER / { N=1; } / " ALTER / { N=1; } / "ANALYZE / { N=1; } / "GRANT / { N=1; } / "COMMENT / { N=1; } / "AUDIT / { N=1; } N==1 { printf "n/n"; N++ } /"$/ { if (N==0) next; s=index( $0, """ ); ln0=length( $0 ) if ( s!=0 ) { lcnt++ if ( lcnt >= 30 ) { ln=substr( $0,s+1,length( substr($0,s+1))-1) t=index( ln, ")," ) if ( t==0 ) { t=index( ln, ", " ) } if ( t==0 ) { t=index( ln, ") " ) } if ( t >0 ) { printf "%sn%s",substr( ln,1,t+1), substr(ln, t+2) lcnt=0 } else { printf "%s", ln if ( ln0< 78 ) { printf "n" ; lcnt=0 } } } else { printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) if ( ln0< 78 ) { printf "n" ; lcnt=0 } } } } END { printf "n/n"} " $* |sed "1,2d; /^$/ d; s/STORAGE *(INI/~ STORAGE (INI/g; s/, "/,~ "/g; s/ ("/~ &/g; s/PCT[FI]/~ &/g; s/[( ]PARTITION /~&/g; s/) TABLESPACE/)~ TABLESPACE/g; s/ , / ,~/g; s/ DATAFILE /&~/" | tr "~" "n" [ZFZHLHRDB1:oracle]:/tmp> [ZFZHLHRDB1:oracle]:/tmp>ksh /tmp/gettabddl.sh /tmp/get_ddl.sql >/tmp/gen_tabddl.sql [ZFZHLHRDB1:oracle]:/tmp>more /tmp/gen_tabddl.sql ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS / CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING / ALTER SESSION SET CURRENT_SCHEMA= "SCOTT" / ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE / ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE / ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO" / 这样运行起来就方便多了。另外，使用imp工具的indexfile选项也可以把dmp文件中的表和索引的创建语句导出而不导入任何对象，命令如下： imp userid/userid@service_name file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n 示例如下所示： [oracle@rhel6lhr tmp]$ 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.3.0 - Production on Wed May 3 21:36:47 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP Export terminated successfully without warnings. [oracle@rhel6lhr tmp]$ imp "/ AS SYSDBA" file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n Import: Release 11.2.0.3.0 - Production on Wed May 3 21:38:10 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set Import terminated successfully without warnings. [oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" REM VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, REM "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL REM DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ; CONNECT SCOTT; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 REM STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ; [oracle@rhel6lhr tmp]$ 可以看到其中的创建表的SQL语句被注释掉了，这个可以用vi命令或者文本工具来处理，处理之后就可以直接使用了。 About Me ............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成 ● 最新修改时间：2018-04-01 06:00 ~ 2018-04-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。