输出object的DDL的方法

最近一段时间，由于经常要通过ssh访问远程环境。所以图形工具使用不了了（以前toad，enterprise manager console就可以轻松搞定 ），而一些troubleshooting 又要求得到一些object的DDL进行分析。一些心得分享给大家（仅在9.2XX验证过）。当然可能有一些疏漏的地方，请xdjm指点:)

一、
1.1 运用DBMS_METADATA.GET_DDL来实现的方法，也是最通用的一种方法。适用于table,index,view,function,procedure,trigger,package(不支持package body)等等。DBMS_METADATA.GET_DDL返回的是一个clob值。可以使用to_char()来转化

例子：
SQL> select to_char(dbms_metadata.get_ddl('TRIGGER','AW_DROP_TRG','SYS')) from dual;

TO_CHAR(DBMS_METADATA.GET_DDL('TRIGGER','AW_DROP_TRG','SYS'))

CREATE OR REPLACE TRIGGER "SYS"."AW_DROP_TRG"
AFTER DROP ON DATABASE
BEGIN
aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
END;
ALTER TRIGGER "SYS"."AW_DROP_TRG" ENABLE

1.2 由于char有4000字符的限制，而且一些view，procedure，function要远超过4000字符。所以写了一个procedure来读取DDL

create or replace procedure get_obj_ddl(
obj_name varchar2,
obj_type varchar2,
obj_owner varchar2)
is
clob_ddl clob;
v_amount number:=250;
v_offset number:=1;
l number;
v_buffer varchar2(250);
begin
--transfer the ddl to the local variable
clob_ddl:=dbms_metadata.get_ddl(obj_type,obj_name,obj_owner);

--get the length of the object.
l:=dbms_lob.getlength(clob_ddl);

--print the ddl in a cycle
dbms_output.enable(200);
for i in 1..ceil(l/v_amount) loop
dbms_lob.read(clob_ddl,v_amount,v_offset,v_buffer);
v_offset:=v_offset+v_amount;
dbms_output.put_line(v_buffer);
end loop;
end;

例子：
SQL> set serveroutput on size 1000000
SQL> exec get_obj_ddl('TEST_1','PROCEDURE','USER1')

CREATE OR REPLACE PROCEDURE "USER1"."TEST_1"
is
b blob;
len integer;
begin
select image_b into b from table_1
where imo_class_c='1.0';
len:=DBMS_LOB.GETLENGTH(b);
dbms_output.put_line('length of blob ' || len);
end;

 

PL/SQL procedure successfully completed.

1.3 最后发现了一个好办法:)，通过set long来设置clob的输出范围，原来一直以为set long只可以设置long的输出，后来竟然偶然发现
原来set long也可以控制clob的输出。long 最大可以设置到2000000000,看来不管什么样的DDL都应该没有问题了,现在看来1.2的做法实在是有点~~~，只是作为一种方法也应该提一下:)

例子：
SQL> set long 2000000000
SQL> select dbms_metadata.get_ddl('PACKAGE','DBMSOBJG_DP','SYS') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','DBMSOBJG_DP','SYS')
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE "SYS"."DBMSOBJG_DP" wrapped 0 abcd abcd abcd abcd ab
cd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 3 9 9200000 1 4 0 19 2 :e:
1PACKAGE: 1DBMSOBJG_DP: 1GENERATE_DDL_DP: 1CANON_SNAME: 1VARCHAR2: 1CANON_ONAME:
1TYPE: 1FLAVOR_COLUMNS: 1DBMS_UTILITY: 1NAME_ARRAY: 1TABLESPACE: 1OUT: 1DDLTAB:
1DBMS_SYS_SQL: 1VARCHAR2S: 1DDL_LENS: 1NUMBER_ARRAY: 1USE_TABLESPACE: 1BOOLEAN:
1CREATE_STORAGE: 1DISABLED_TRIG: 1FORCE_VIEW: 1DEBUG_TABLE: 1DDLCNT: 1INTEGER:

…………………………………………………………………………………………

 

 

二、从dba_views(user_views)抽取view的定义。

SQL> set pagesize 1000
SQL> select text from dba_views where view_name='ALL_APPLY';

TEXT
--------------------------------------------------------------------------------
select a.apply_name, a.queue_name, a.queue_owner, a.apply_captured,
a.rule_set_name, a.rule_set_owner, a.apply_user, a.apply_database_link,
a.apply_tag, a.ddl_handler, a.message_handler, a.status
from dba_apply a, all_queues q
where a.queue_name = q.name
and a.queue_owner = q.owner
and a.rule_set_name is null
and a.rule_set_owner is null
union all
select a.apply_name, a.queue_name, a.queue_owner, a.apply_captured,
a.rule_set_name, a.rule_set_owner, a.apply_user, a.apply_database_link,
a.apply_tag, a.ddl_handler, a.message_handler, a.status
from dba_apply a, all_queues q, all_rule_sets r
where a.queue_name = q.name
and a.queue_owner = q.owner
and a.rule_set_name is not null
and a.rule_set_name = r.rule_set_name
and a.rule_set_owner is not null
and a.rule_set_owner = r.rule_set_owner

 

 

三、前面已经说过了package body,java source等一些大型的ddl也不可用dbms_metadata.get_ddl的方法取得。不过幸好我们还有dba_source(user_source)

SQL> select type,count(*) from dba_source group by type;

TYPE COUNT(*)
------------ ----------
FUNCTION 576
JAVA SOURCE 2356
PACKAGE 78938
PACKAGE BODY 60726
PROCEDURE 2080
TRIGGER 867
TYPE 8514
TYPE BODY 744

8 rows selected.

从以上的语句我们也可以看出dba_source可以支持的几种对象类型，当然最有用的还是那个package body。而package,procedure,function,type,trigger都是可以通过dbms_metadata.get_ddl的方法得到的。

例子：

SQL> set pagesize 10000
SQL> col text for a1000
SQL> set lin 2000
SQL> select text from dba_source where name='CONNECTIONINTERFACE' and owner='SYS' and type='PACKAGE BODY' order by line;

TEXT
-------------------------------------------------------------------------------------------------------------
PACKAGE BODY ConnectionInterface AS
PROCEDURE interrupt(objectID IN NUMBER) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_interrupt"
PARAMETERS(context, objectID) WITH CONTEXT;
PROCEDURE getConnectionStruct(objectID IN NUMBER,
ret OUT SQLConnectionStruct, except OUT SQLOLAPIException)
AS EXTERNAL LIBRARY DBMS_OLAPI_LIB
NAME "Connection_getConnectionStruct"
PARAMETERS(context, objectID, ret, ret INDICATOR STRUCT, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE getModuleEVersion(objectID IN NUMBER,
moduleName IN NVARCHAR2, ret OUT NVARCHAR2,
-- moduleName IN SQLWCharSeq, ret OUT SQLWstring,
except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_getModuleEVersion"
-- PARAMETERS(context, objectID, moduleName, ret, ret INDICATOR STRUCT,
PARAMETERS(context, objectID, moduleName, ret OCIString, ret INDICATOR,
except, except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE shareSession(objectID IN NUMBER,
ret OUT INTEGER, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_shareSession"
PARAMETERS(context, objectID, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE hasBeenShared(objectID IN NUMBER,
ret OUT SMALLINT, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_hasBeenShared"
PARAMETERS(context, objectID, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE executeCommand(objectID IN NUMBER, command IN NVARCHAR2,
-- PROCEDURE executeCommand(objectID IN NUMBER, command IN SQLWCharSeq,
id IN INTEGER, ret OUT NVARCHAR2, except OUT SQLOLAPIException)
AS EXTERNAL LIBRARY DBMS_OLAPI_LIB NAME "Connection_executeCommand"
-- PARAMETERS(context, objectID, command, id, ret, ret INDICATOR STRUCT,
PARAMETERS(context, objectID, command OCIString, id, ret OCIString,
ret INDICATOR, except, except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateIntegerExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT INTEGER, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateIntegerExpr"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateShortIntegerExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT SMALLINT, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateShortIntege"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateDecimalExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT DOUBLE PRECISION, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateDecimalExpr"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateShortDecimalExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT FLOAT, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateShortDecima"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateStringExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT NVARCHAR2, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateStringExpre"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret OCIString, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateStringListExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT SQLWstringSequence, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateStringListE"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR STRUCT, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateBooleanExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT SMALLINT, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateBooleanExpr"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE evaluateDateExpression(objectID IN NUMBER,
expression IN NVARCHAR2, naOut OUT SMALLINT, id IN INTEGER,
-- expression IN SQLWCharSeq, naOut OUT SMALLINT, id IN INTEGER,
ret OUT INTEGER, except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_evaluateDateExpress"
PARAMETERS(context, objectID, expression OCIString, naOut,
naOut INDICATOR, id, ret, ret INDICATOR, except,
except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE getConnectionParameterInfo(objectID IN NUMBER,
databaseName IN NVARCHAR2, parameters IN SQLPropertySequence,
-- databaseName IN SQLWCharSeq, parameters IN SQLPropertySequence,
ret OUT SQLConnectionParameterInfoSequ, except OUT SQLOLAPIException)
AS EXTERNAL LIBRARY DBMS_OLAPI_LIB
NAME "Connection_getConnectionParame" PARAMETERS(context,
objectID, databaseName, parameters, ret, ret INDICATOR STRUCT, except,
except INDICATOR STRUCT) WITH CONTEXT;
FUNCTION openDatabase(objectID IN NUMBER, databaseName IN NVARCHAR2,
-- FUNCTION openDatabase(objectID IN NUMBER, databaseName IN SQLWCharSeq,
parameters IN SQLPropertySequence, ret OUT VARCHAR2,
except OUT SQLOLAPIException) RETURN NUMBER AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_openDatabase"
PARAMETERS(context, objectID, databaseName, parameters, ret OCIString,
ret INDICATOR, RETURN INDICATOR, except, except INDICATOR STRUCT)
WITH CONTEXT;
FUNCTION getDefaultDatabase(objectID IN NUMBER, ret OUT VARCHAR2,
except OUT SQLOLAPIException) RETURN NUMBER AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_getDefaultDatabase"
PARAMETERS(context, objectID, ret OCIString, ret INDICATOR,
RETURN INDICATOR, except, except INDICATOR STRUCT) WITH CONTEXT;
PROCEDURE close(objectID IN NUMBER, except OUT SQLOLAPIException)
AS EXTERNAL LIBRARY DBMS_OLAPI_LIB NAME "Connection_close"
PARAMETERS(context, objectID, except, except INDICATOR STRUCT)
WITH CONTEXT;
PROCEDURE respond(objectID IN NUMBER, response IN VARCHAR2,
except OUT SQLOLAPIException) AS EXTERNAL
LIBRARY DBMS_OLAPI_LIB NAME "Connection_respond"
PARAMETERS(context, objectID, response OCIString, except,
except INDICATOR STRUCT) WITH CONTEXT;
END;

130 rows selected.

SQL>