最近一段时间，由于经常要通过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

?

?