Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？
QQ群里有人问：如何导出一个用户下的存储过程？
麦苗答：方法有多种，可以使用DBMS_MetaDATA.GET_DDL包。
使用如下的脚本即可导出某个用户下的存储过程代码到/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> DESC DBMS_MetaDATA.GET_DDLPARAMETER 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
其详细参数如下：
lNAME对象名称
lVERSION对象原数据的版本
lTRANSFORM默认值为DDL
l查看创建表sql语句：
SELECT DBMS_MetaDATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;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)FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'VIEW';
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)FROM DBA_SCHEDULER_JOBS D
WHERE D.JOB_TYPE = 'STORED_PROCEDURE'
AND D.STATE = 'SCHEDULED'
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 UWHERE 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')--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),0);"> "LOC" VARCHAR2(13),0);"> CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
SYS@lhrdb>EXECUTE DBMS_MetaDATA.SET_TRANSFORM_PARAM(DBMS_MetaDATA.SESSION_TRANSFORM,FALSE);
PL/sql procedure successfully completed.
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 0SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FeedBACK OFF
SET Feed OFF;
SET ECHO OFF
SPOOL /tmp/schema_scott.sql
SELECT CASE
WHEN U.OBJECT_TYPE IN
('PROCEDURE','FUNCTION'，'PACKAGE','TRIGGER') THEN
DBMS_MetaDATA.GET_DDL(U.OBJECT_TYPE,U.OBJECT_NAME,U.OWNER) ||
CHR(10) || '/'
ELSE
CHR(10) || ';'
END AS SCOTT_DDL
FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE IN
('TABLE','TRIGGER')
AND U.OWNER='SCOTT';
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_355_404@sqlplus<<eof <="" eof
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
(
--Convert DBA_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 Java installed.
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
) object_type
from dba_objects
where owner in ('LHR')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',0);"> 'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION','PROGRAM')
--Ignore system-generated types that support collection processing.
and not (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,table_name from dba_tables where iot_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_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=MetaDATA_ONLY SCHEMAS=SCOTT EXCLUDE=STATISTICSimpdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log sqlFILE=expddl_lhr.sql
[ZFZHLHRDB1:oracle]:/oracle>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmpExport: 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,0);">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
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
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
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,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 ';
-- 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
CREATE TABLE "SCOTT"."DEPT"
"DNAME" VARCHAR2(14 BYTE),0);"> "LOC" VARCHAR2(13 BYTE)
TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."EMP"
( "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)
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10 BYTE),0);"> "SAL" NUMBER,0);"> "COMM" NUMBER
) SEGMENT CREATION DEFERRED
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,0);"> "LOSAL" NUMBER,0);"> "HISAL" NUMBER
CREATE TABLE "SCOTT"."TEST"
( "DUMMY" VARCHAR2(1 BYTE)
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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,0);">:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,:16,:17)';
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'PK_DEPT';
i_o := 'SCOTT';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,I_O,4,1,TO_DATE('2016-07-07 22:00:11',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"','"IMPDP_STATS"','"SYS"');
/
《《《《。。。。。。。。篇幅原因，有省略,剩下的都是统计信息，生成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=Nimp \'/ 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=nExport: Release 11.2.0.4.0 - Production on Tue Aug 2 15:42:11 2016
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.
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
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,"ENAME" VARCHAR2(10),"JOB" VARCH"
"AR2(9),"MGR" NUMBER(4,"HIREDATE" DATE,"SAL" NUMBER(7,"COMM" NUM"
"BER(7,"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 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.sqlmore /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 ) {
printf "%s\n%s",substr( ln,t+1),substr(ln,t+2)
lcnt=0
}
else {
printf "%s",ln
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/ DATAFILE /&~/' | tr "~" "\n"
[ZFZHLHRDB1:oracle]:/tmp>
ksh /tmp/gettabddl.sh /tmp/get_ddl.sql > /tmp/gen_tabddl.sql
more /tmp/gen_tabddl.sql
ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
CREATE TABLE "EMP"
("EMPNO" NUMBER(4,0);"> "ENAME" VARCHAR2(10),0);"> "JOB" VARCHAR2(9),0))
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" )
TABLESPACE "USERS" LOGGING
ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY
("EMPNO") USING INDEX
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=NExport: Release 11.2.0.3.0 - Production on Wed May 3 21:36:47 2017
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
and Real Application Testing options
[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
[oracle@rhel6lhr tmp]$ more /tmp/get_ti_ddl.sql
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4,"ENAME"
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
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宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群 小麦苗的微店
.............................................................................................................................................
