怎樣得到建表(儲存過程,函式等)的DDL指令碼,請看案例

最近不斷有同事在現網操作時問我同樣的問題,那就是怎麼樣得到建表(儲存過程,函式等)的DDL指令碼,下面總結兩種方法給大家,供大家學習!

第一個方法:查詢user_source資料字典可獲得.

SQL> desc user_source
Name Type Nullable Default Comments
---- -------------- -------- ------- -------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the object
TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of source
TEXT VARCHAR2(4000) Y Source text

不過user_source只能查詢FUNCTION,PACKAGE,PACKAGE BODY,PROCEDURE,TRIGGER的源,而不能檢視table等物件的ddl.

第二個方法:呼叫dbms_metadata.get_ddl(object_type,name),即
select dbms_metadata.get_ddl(object_type,name) from dual;

要注意的是,需設定一下環境變數,如下:

set linesize 10000

set pagesize 0

set long 100000

set feedback off

等等.

[@more@]