Oracle中如何導出存儲過程、函數、包和觸發器的定義語句？如何導出表的結構？如何導出索引的創建語句？

• 2019 年 10 月 10 日
• 筆記

今天小麥苗給大家分享的是Oracle中如何導出存儲過程、函數、包和觸發器的定義語句？如何導出表的結構？如何導出索引的創建語句？。

Oracle中如何導出存儲過程、函數、包和觸發器的定義語句？如何導出表的結構？如何導出索引的創建語句？

注意：本文內容太多，公眾號有字數限制，全文可以參考http://blog.itpub.net/26736162/viewspace-2152892/或者去小麥苗的雲盤下載pdf版本閱讀，謝謝大家的理解。

QQ群里有人問：如何導出一個用戶下的存儲過程？ 麥苗答：方法有多種，可以使用DBMS_METADATA.GET_DDL包。

總體來說有兩種方式來獲取，第一，利用系統包DBMS_METADATA包中的GET_DDL函數來獲取，第二，利用exp或expdp來獲取。 下面來看第一種方式，如何利用系統包DBMS_METADATA包中的GET_DDL函數來獲取對象的定義語句。下面是該函數的入參和出參： SQL> DESC DBMS_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 NAME 對象名稱 l VERSION 對象原數據的版本 l TRANSFORM 默認值為DDL l 查看創建表SQL語句： SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U; n 查看創建主鍵的SQL語句： SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') 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) 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', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'TRIGGER'; n 查看創建包（PACKAGE）的SQL語句： SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE = 'PACKAGE'; n 查看創建同義詞（SYNONYM）的SQL語句： SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME) FROM USER_OBJECTS U 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;line-height: normal"> 得到一個用戶下的所有表、索引、存儲過程、函數的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> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') ——————————————————————————– CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), 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 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" SYS@lhrdb> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE); PL/SQL procedure successfully completed. SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') ——————————————————————————– CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 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。 現在來回答本題目中的問題，程式碼如下所示： SET PAGESIZE 0 SET TRIMSPOOL ON SET LINESIZE 10000 SET LONG 90000 SET FEEDBACK OFF SET FEED OFF; SET ECHO OFF SPOOL SCHEMA_SCOTT.SQL SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME,U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION'，'PACKAGE','TRIGGER') AND U.OWNER='SCOTT'; SPOOL OFF; 可以使用如下的SQL腳本生成某個用戶下的所有對象的DDL語句： 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, 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 from dba_objects where owner in ('LHR') –These objects are included with other object types. and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION', '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, their DDL is part of their parent table. and (owner, object_name) not in (select owner, 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=STATISTICS 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 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命令或者文本工具來處理，處理之後就可以直接使用了。

• 使用如下的腳本即可導出某個用戶下的存儲過程程式碼到/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 打開文件後，簡單處理一下即可。
• 使用如下的腳本即可導出某個用戶下的存儲過程程式碼到/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 打開文件後，簡單處理一下即可。

注意：本文內容太多，公眾號有字數限制，全文可以參考http://blog.itpub.net/26736162/viewspace-2152892/或者去小麥苗的雲盤下載pdf版本閱讀，謝謝大家的理解。

omaimiaolhr，學習最實用的資料庫技術。