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

• 
  使用如下的脚本即可导出某个用户下的存储过程代码到/tmp/a.sql文件中：
  
  @H_403_19@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

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

  ---

  
  

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

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

  sql> DESC DBMS_MetaDATA.GET_DDL

  @H_403_19@PARAMETER TYPE MODE DEFAULT?

  @H_403_19@----------- -------- ---- --------

  @H_403_19@(RESULT) CLOB

  @H_403_19@OBJECT_TYPE VARCHAR2 IN

  @H_403_19@NAME VARCHAR2 IN

  @H_403_19@SCHEMA VARCHAR2 IN Y

  @H_403_19@VERSION VARCHAR2 IN Y

  @H_403_19@MODEL VARCHAR2 IN Y

  @H_403_19@TRANSFORM VARCHAR2 IN Y

  其详细参数如下：

  lNAME对象名称

  lVERSION对象原数据的版本

  lTRANSFORM默认值为DDL

  l查看创建表sql语句：

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

  @H_403_19@n查看创建主键的sql语句：

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

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

  SELECT DBMS_MetaDATA.GET_DDL('VIEW','MY_TABLES',U.OBJECT_NAME)

  @H_403_19@ FROM USER_OBJECTS U

  @H_403_19@WHERE OBJECT_TYPE = 'VIEW';

  @H_403_19@SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');

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

  SELECT DBMS_MetaDATA.GET_DDL('TRIGGER',0);">WHERE OBJECT_TYPE = 'TRIGGER';

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

  SELECT DBMS_MetaDATA.GET_DDL('PACKAGE',0);">WHERE OBJECT_TYPE = 'PACKAGE';

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

  SELECT DBMS_MetaDATA.GET_DDL('SYNONYM',0);">WHERE OBJECT_TYPE = 'SYNONYM';

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

  SELECT DBMS_MetaDATA.GET_DDL('ROLE',U.ROLE) FROM DBA_ROLES U;

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

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

  @H_403_19@ FROM DBA_SCHEDULER_JOBS D

  @H_403_19@WHERE D.JOB_TYPE = 'STORED_PROCEDURE'

  @H_403_19@ AND D.STATE = 'SCHEDULED'

  @H_403_19@ AND D.SCHEDULE_NAME IS NULL;

  n<span "="" style="word-wrap: break-word; font-family: "courier new"; line-height: normal;">得到一个用户下的所有表、索引、存储过程、函数的DDL语句：

  SELECT DBMS_MetaDATA.GET_DDL(U.OBJECT_TYPE,0);">FROM USER_OBJECTS U

  @H_403_19@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> SELECT DBMS_MetaDATA.GET_DDL('TABLE',0);">DBMS_MetaDATA.GET_DDL('TABLE','SCOTT')

  @H_403_19@--------------------------------------------------------------------------------

  @H_403_19@ CREATE TABLE "SCOTT"."DEPT"

  @H_403_19@ ( "DEPTNO" NUMBER(2,0),

  @H_403_19@ "DNAME" VARCHAR2(14),0);"> "LOC" VARCHAR2(13),0);"> CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  @H_403_19@ USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  @H_403_19@ STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  @H_403_19@ PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  @H_403_19@ BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  @H_403_19@ TABLESPACE "USERS" ENABLE

  @H_403_19@ ) SEGMENT CREATION IMMEDIATE

  @H_403_19@ PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  @H_403_19@NOCOMPRESS LOGGING

  @H_403_19@ TABLESPACE "USERS"

  @H_403_19@SYS@lhrdb>EXECUTE DBMS_MetaDATA.SET_TRANSFORM_PARAM(DBMS_MetaDATA.SESSION_TRANSFORM,FALSE);

  @H_403_19@PL/sql procedure successfully completed.

  @H_403_19@ TABLESPACE "USERS"

  使用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中显示不全，则需要set long 9999。

  （4）对于DBMS_MetaDATA.GET_DDL包，可以在PLsql Developer工具中运行，也可以在sql*Plus中运行。

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

  SET PAGESIZE 0

  @H_403_19@SET TRIMSPOOL ON

  @H_403_19@SET LINESIZE 10000

  @H_403_19@SET LONG 90000

  @H_403_19@SET FeedBACK OFF

  @H_403_19@SET Feed OFF;

  @H_403_19@SET ECHO OFF

  @H_403_19@SPOOL /tmp/schema_scott.sql

  @H_403_19@SELECT CASE

  @H_403_19@ WHEN U.OBJECT_TYPE IN

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

  @H_403_19@ DBMS_MetaDATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME,U.OWNER) ||

  @H_403_19@ CHR(10) || '/'

  @H_403_19@ ELSE

  @H_403_19@403_19@ CHR(10) || ';'

  @H_403_19@ END AS SCOTT_DDL

  @H_403_19@ FROM DBA_OBJECTS U

  @H_403_19@WHERE U.OBJECT_TYPE IN

  @H_403_19@ ('TABLE','TRIGGER')

  @H_403_19@AND U.OWNER='SCOTT';

  @H_403_19@SPOOL OFF;

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

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

  AND U.OWNER='SCOTT';

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

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

  @H_472_404@ @H_403_19@sqlplus<<eof <="" eof

  @H_403_19@set long 100000

  @H_403_19@set head off

  @H_403_19@set echo off

  @H_403_19@set pagesize 0

  @H_403_19@set verify off

  @H_403_19@set Feedback off

  @H_403_19@spool schema.out

  @H_403_19@

  @H_403_19@select dbms_Metadata.get_ddl(object_type,object_name,owner)

  @H_403_19@from

  @H_403_19@(

  @H_403_19@ --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_MetaDATA object type:

  @H_403_19@ select

  @H_403_19@ owner,

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

  @H_403_19@ --That code is not included since many database don't have Java installed.

  @H_403_19@ object_name,0);"> decode(object_type,0);"> 'DATABASE LINK','DB_LINK',0);"> 'JOB','PROCOBJ',0);"> 'RULE SET',0);"> 'RULE',0);"> 'EVALUATION CONTEXT',0);"> 'PACKAGE','PACKAGE_SPEC',0);"> 'PACKAGE BODY','PACKAGE_BODY',0);"> 'TYPE','TYPE_SPEC',0);"> 'TYPE BODY','TYPE_BODY',0);"> 'MATERIALIZED VIEW','MATERIALIZED_VIEW',0);"> 'QUEUE','AQ_QUEUE',0);"> 'JAVA CLASS','JAVA_CLASS',0);"> 'JAVA TYPE','JAVA_TYPE',0);"> 'JAVA SOURCE','JAVA_SOURCE',0);"> 'JAVA RESOURCE','JAVA_RESOURCE',0);"> object_type

  @H_403_19@ ) object_type

  @H_403_19@ from dba_objects

  @H_403_19@ where owner in ('LHR')

  @H_403_19@ --These objects are included with other object types.

  @H_403_19@ and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',0);"> 'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION','PROGRAM')

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

  @H_403_19@ and not (object_type = 'TYPE' and object_name like 'SYS_PLsql_%')

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

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

  @H_403_19@ --Exlclude overflow segments,table_name from dba_tables where iot_type = 'IOT_OVERFLOW')

  @H_403_19@)

  @H_403_19@order by owner,object_type,object_name;

  @H_403_19@spool off

  @H_403_19@quit

  @H_403_19@EOF

  @H_403_19@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

  @H_403_19@impdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=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

  @H_403_19@

  @H_403_19@Export: Release 11.2.0.4.0 - Production on Wed Aug 3 15:14:55 2016

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

  @H_403_19@Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

  @H_403_19@With the Partitioning,Real Application Clusters,Automatic Storage Management,OLAP,0);">Data Mining and Real Application Testing options

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

  @H_403_19@Estimate in progress using BLOCKS method...

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

  @H_403_19@Total estimation using BLOCKS method: 256 KB

  @H_403_19@Processing object type SCHEMA_EXPORT/USER

  @H_403_19@Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

  @H_403_19@Processing object type SCHEMA_EXPORT/ROLE_GRANT

  @H_403_19@Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

  @H_403_19@Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/TABLE

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

  @H_403_19@Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

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

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

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

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

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

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

  @H_403_19@******************************************************************************

  @H_403_19@Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

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

  @H_403_19@Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20

  @H_403_19@impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

  @H_403_19@Import: Release 11.2.0.4.0 - Production on Wed Aug 3 15:16:06 2016

  @H_403_19@Master table "SYS"."SYS_sql_FILE_FULL_01" successfully loaded/unloaded

  @H_403_19@Starting "SYS"."SYS_sql_FILE_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql

  @H_403_19@Job "SYS"."SYS_sql_FILE_FULL_01" successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02

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

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

  @H_403_19@-- CONNECT SYS

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

  @H_403_19@ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER,0);">ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER,0);">ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER,0);">ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER,0);">ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER,LEVEL 192 ';

  @H_403_19@-- new object type path: SCHEMA_EXPORT/USER

  @H_403_19@-- CONNECT SYSTEM

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

  @H_403_19@ DEFAULT TABLESPACE "USERS"

  @H_403_19@ TEMPORARY TABLESPACE "TEMP"

  @H_403_19@ PASSWORD EXPIRE

  @H_403_19@ ACCOUNT LOCK;

  @H_403_19@-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

  @H_403_19@GRANT UNLIMITED TABLESPACE TO "SCOTT";

  @H_403_19@-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

  @H_403_19@GRANT "CONNECT" TO "SCOTT";

  @H_403_19@GRANT "RESOURCE" TO "SCOTT";

  @H_403_19@-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

  @H_403_19@ALTER USER "SCOTT" DEFAULT ROLE ALL;

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

  @H_403_19@-- CONNECT SCOTT

  @H_403_19@BEGIN

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

  @H_403_19@COMMIT;

  @H_403_19@END;

  @H_403_19@/

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

  @H_403_19@CREATE TABLE "SCOTT"."DEPT"

  @H_403_19@ "DNAME" VARCHAR2(14 BYTE),0);"> "LOC" VARCHAR2(13 BYTE)

  @H_403_19@ TABLESPACE "USERS" ;

  @H_403_19@CREATE TABLE "SCOTT"."EMP"

  @H_403_19@ ( "EMPNO" NUMBER(4,0);"> "ENAME" VARCHAR2(10 BYTE),0);"> "JOB" VARCHAR2(9 BYTE),0);"> "MGR" NUMBER(4,0);"> "HIREDATE" DATE,0);"> "SAL" NUMBER(7,2),0);"> "COMM" NUMBER(7,0);"> "DEPTNO" NUMBER(2,0)

  @H_403_19@CREATE TABLE "SCOTT"."BONUS"

  @H_403_19@ ( "ENAME" VARCHAR2(10 BYTE),0);"> "SAL" NUMBER,0);"> "COMM" NUMBER

  @H_403_19@ ) SEGMENT CREATION DEFERRED

  @H_403_19@CREATE TABLE "SCOTT"."SALGRADE"

  @H_403_19@ ( "GRADE" NUMBER,0);"> "LOSAL" NUMBER,0);"> "HISAL" NUMBER

  @H_403_19@CREATE TABLE "SCOTT"."TEST"

  @H_403_19@ ( "DUMMY" VARCHAR2(1 BYTE)

  @H_403_19@STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

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

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

  @H_403_19@ PCTFREE 10 INITRANS 2 MAXTRANS 255

  @H_403_19@ TABLESPACE "USERS" PARALLEL 1 ;

  @H_403_19@ ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;

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

  @H_403_19@ ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;

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

  @H_403_19@ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  @H_403_19@ USING INDEX "SCOTT"."PK_DEPT" ENABLE;

  @H_403_19@ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  @H_403_19@ USING INDEX "SCOTT"."PK_EMP" ENABLE;

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

  @H_403_19@DECLARE I_N VARCHAR2(60);

  @H_403_19@ I_O VARCHAR2(60);

  @H_403_19@ NV VARCHAR2(1);

  @H_403_19@ c DBMS_MetaDATA.T_VAR_COLL;

  @H_403_19@ df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

  @H_403_19@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,0);">:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,:16,:17)';

  @H_403_19@ DELETE FROM "SYS"."IMPDP_STATS";

  @H_403_19@ i_n := 'PK_DEPT';

  @H_403_19@ i_o := 'SCOTT';

  @H_403_19@ EXECUTE IMMEDIATE stmt USING 2,I_N,NV,I_O,4,1,TO_DATE('2016-07-07 22:00:11',df),NV;

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

  @H_403_19@/

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

  
  @H_403_19@
  

  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

  @H_403_19@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

  @H_403_19@Export: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:11 2016

  @H_403_19@Data Mining and Real Application Tes

  @H_403_19@Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

  @H_403_19@Note: table data (rows) will not be exported

  @H_403_19@About to export specified tables via Conventional Path ...

  @H_403_19@Current user changed to SCOTT

  @H_403_19@. . exporting table EMP

  @H_403_19@Export terminated successfully without warnings.

  @H_403_19@imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp show=y log=/tmp/get_ddl.sql buffer=20480000 full=y

  @H_403_19@Import: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:44 2016

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

  @H_403_19@import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

  @H_403_19@. importing SYS's objects into SYS

  @H_403_19@. importing SCOTT's objects into SCOTT

  @H_403_19@"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""

  @H_403_19@"CREATE TABLE "EMP" ("EMPNO" NUMBER(4,"ENAME" VARCHAR2(10),"JOB" VARCH"

  @H_403_19@"AR2(9),"MGR" NUMBER(4,"HIREDATE" DATE,"SAL" NUMBER(7,"COMM" NUM"

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

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

  @H_403_19@"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"

  @H_403_19@"CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX"

  @H_403_19@"TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"

  @H_403_19@"IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING"

  @H_403_19@"ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE"

  @H_403_19@"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN"

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

  @H_403_19@"ERS" LOGGING ENABLE "

  @H_403_19@"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"

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

  @H_403_19@"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""

  @H_403_19@Import terminated successfully without warnings.

  @H_403_19@[ZFZHLHRDB1:oracle]:/oracle>

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

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

  @H_403_19@more /tmp/gettabddl.sh

  @H_403_19@awk '

  @H_403_19@ / \"BEGIN / { N=1; }

  @H_403_19@ / \"CREATE / { N=1; }

  @H_403_19@ / \"CREATE INDEX/ { N=1; }

  @H_403_19@ / \"CREATE UNIQUE INDEX/ { N=1; }

  @H_403_19@ / \"ALTER / { N=1; }

  @H_403_19@ / \" ALTER / { N=1; }

  @H_403_19@ / \"ANALYZE / { N=1; }

  @H_403_19@ / \"GRANT / { N=1; }

  @H_403_19@ / \"COMMENT / { N=1; }

  @H_403_19@ / \"AUDIT / { N=1; }

  @H_403_19@ N==1 { printf "\n/\n"; N++ }

  @H_403_19@ /\"$/ {

  @H_403_19@ if (N==0) next;

  @H_403_19@ s=index( $0,"\"" );

  @H_403_19@ ln0=length( $0 )

  @H_403_19@ if ( s!=0 ) {

  @H_403_19@ lcnt++

  @H_403_19@ if ( lcnt >= 30 ) {

  @H_403_19@ ln=substr( $0,s+1,length( substr($0,s+1))-1)

  @H_403_19@ t=index( ln,")," )

  @H_403_19@ if ( t==0 ) { t=index( ln,"," ) }

  @H_403_19@

  @H_403_19@ if ( t > 0 ) {

  @H_403_19@ printf "%s\n%s",substr( ln,t+1),substr(ln,t+2)

  @H_403_19@ lcnt=0

  @H_403_19@ }

  @H_403_19@ else {

  @H_403_19@ printf "%s",ln

  @H_403_19@ if ( ln0 < 78 ) { printf "\n" ; lcnt=0 }

  @H_403_19@403_19@ END { printf "\n/\n"}

  @H_403_19@' $* |sed '1,2d; /^$/ d;

  @H_403_19@s/STORAGE *(INI/~ STORAGE (INI/g;

  @H_403_19@s/,"/,~ "/g;

  @H_403_19@s/ (\"/~ &/g;

  @H_403_19@s/PCT[FI]/~ &/g;

  @H_403_19@s/[( ]PARTITION /~&/g;

  @H_403_19@s/) TABLESPACE/)~ TABLESPACE/g;

  @H_403_19@403_19@s/ DATAFILE /&~/' | tr "~" "\n"

  @H_403_19@[ZFZHLHRDB1:oracle]:/tmp>

  @H_403_19@ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql

  @H_403_19@more /tmp/gen_tabddl.sql

  @H_403_19@ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"

  @H_403_19@CREATE TABLE "EMP"

  @H_403_19@ ("EMPNO" NUMBER(4,0);"> "ENAME" VARCHAR2(10),0);"> "JOB" VARCHAR2(9),0))

  @H_403_19@ STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  @H_403_19@ TABLESPACE "USERS" LOGGING NOCOMPRESS

  @H_403_19@CREATE UNIQUE INDEX "PK_EMP" ON "EMP"

  @H_403_19@ ("EMPNO" )

  @H_403_19@ TABLESPACE "USERS" LOGGING

  @H_403_19@ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY

  @H_403_19@ ("EMPNO") USING INDEX

  @H_403_19@ TABLESPACE "USERS" LOGGING ENABLE

  @H_403_19@ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY

  @H_403_19@ ("DEPTNO") REFERENCES "DEPT"

  @H_403_19@ ("DEPTNO") ENABLE NOVALIDATE

  @H_403_19@ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO"

  @H_403_19@/

  这样运行起来就方便多了。

  另外，使用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

  @H_403_19@Export: Release 11.2.0.3.0 - Production on Wed May 3 21:36:47 2017

  @H_403_19@Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

  @H_403_19@403_19@and Real Application Testing options

  @H_403_19@[oracle@rhel6lhr tmp]$ imp \'/ AS SYSDBA\' file=/tmp/exp_ddl_lhr_01.dmp FULL=Y indexfile=/tmp/get_ti_ddl.sql rows=n

  @H_403_19@Import: Release 11.2.0.3.0 - Production on Wed May 3 21:38:10 2017

  @H_403_19@[oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql

  @H_403_19@REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,"ENAME"

  @H_403_19@REM VARCHAR2(10),"JOB" VARCHAR2(9),0);">REM "SAL" NUMBER(7,"COMM" NUMBER(7,0);">REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536

  @H_403_19@REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL

  @H_403_19@REM DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;

  @H_403_19@CONNECT SCOTT;

  @H_403_19@CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10

  @H_403_19@INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

  @H_403_19@FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

  @H_403_19@LOGGING ;

  @H_403_19@REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY

  @H_403_19@REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  @H_403_19@REM STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST

  @H_403_19@REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ;

  @H_403_19@REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY

  @H_403_19@REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ;

  @H_403_19@REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;

  @H_403_19@[oracle@rhel6lhr tmp]$

  可以看到其中的创建表的sql语句被注释掉了，这个可以用vi命令或者文本工具来处理，处理之后就可以直接使用了。

  @H_403_19@
  
  @H_403_19@
  
  @H_403_19@
  

  ---

  ---

  About Me

  @H_403_19@............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ @H_403_19@● 本文博客园地址：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 @H_403_19@● 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/ @H_403_19@使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。 小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群 小麦苗的微店 .............................................................................................................................................