Oracle 查看 表 存储过程 触发器 函数 等对象定义语句的方法

有时候，我们想查看表，存储，触发器等对象的定语语句，有以下两种方法：

1.查all_source表

2.用DBMS_METADATA包。

一．通过all_source表

先来确认下，通过all_source表可以查看哪些类型的对象：

SQL>SELECTdistincttypeFROMALL_SOURCE;

TYPE

------------

PROCEDURE

PACKAGE

PACKAGEBODY

LIBRARY

TYPEBODY

TRIGGER

FUNCTION

JAVASOURCE

TYPE

从以上的结果我们可以看到，我们可以通过该表查询的对象.

查看存储过程定义语句：

SQL>SELECTtextFROMALL_SOURCEwhereTYPE='PROCEDURE'ANDNAME='ADDCUSTBUSS';

TEXT

--------------------------------------------------------------------------------

PROCEDUREaddcustbuss(

acustidINcustbuss.custid%TYPE,

bussnameINcustbuss.businessname%TYPE,

aopidINcustbuss.opid%TYPE,

acreatetimeINcustbuss.createtime%TYPE,

aCustTelINcustbuss.CustTel%TYPE,--客户电话

aContactINcustbuss.Contact%TYPE,--联系人

aFeedbackINcustbuss.Feedback%TYPE,--客户反馈

asidOUTcustbuss.ID%TYPE,

RESULTOUTINTEGER

)

IS

BEGIN

RESULT:=-1;

SELECTgetarea||TO_CHAR(idseq.NEXTVAL,'FM0999999999')

INTOasid

FROMDUAL;

INSERTINTOcustbuss

(ID,custid,businessname,opid,createtime,CustTel,Contact,Feedback

)

VALUES(asid,acustid,bussname,aopid,acreatetime,aCustTel,aContact,aFeedback

);

RESULT:=0;

EXCEPTION

WHENOTHERS

THEN

RESULT:=-1;

ENDaddcustbuss;

已选择32行。

SQL>

查看触发器定义语句

SQL>SELECTtextFROMALL_SOURCEwhereTYPE='TRIGGER'ANDNAME='TRDB_TEAM';

TEXT

-----------------------------------------------------------------------------

TRIGGER"NEWCCS".trdb_team

BEFOREDELETE

ONteam

REFERENCINGNEWASNEWOLDASOLD

FOREACHROW

BEGIN

DELETEFROMteam_teamgroup_map

WHEREteamid=:OLD.teamid;

ENDtrdb_team;

已选择10行。

方法也比较简单，修改TYPE和NAME就可以。注意要大写。

二．通过DBMS_METADATA包

Oracle的在线文档，对这个包有详细说明：

DBMS_METADATA

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#ARPLS640

通过该dbms_metadata包的get_ddl（）方法，我们可以查看表，索引，视图，存储过程等的定义语句。
用法：

SQL>selectdbms_metadata.get_ddl('对象类型','名称','用户名')fromdual;

1.查看表的定义语句
SQL>setlong9999999

SQL>selectdbms_metadata.get_ddl('TABLE','BBS','NEWCCS')fromdual;

DBMS_METADATA.GET_DDL('TABLE','BBS','NEWCCS')

--------------------------------------------------------------------------------

CREATETABLE"NEWCCS"."BBS"

("BBSID"NUMBER(16,0),

"SENDER"VARCHAR2(20)NOTNULLENABLE,

"INCEPT"VARCHAR2(20)NOTNULLENABLE,

"MSGTITLE"VARCHAR2(40)NOTNULLENABLE,

"MSG"VARCHAR2(500),

"SENDTIME"DATEDEFAULTsysdateNOTNULLENABLE,

"MSGTOP"VARCHAR2(1)DEFAULT0,

"MSGFILENAME"VARCHAR2(100),

"MSGFILE"LONGRAW,

"EDITTIME"DATEDEFAULTsysdate,

CONSTRAINT"PK_BBS"PRIMARYKEY("BBSID")

USINGINDEXPCTFREE10INITRANS2MAXTRANS255COMPUTESTATISTICS

STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645

PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHE

DEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"NEWCCS"ENABLE

)SEGMENTCREATIONIMMEDIATE

PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING

STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645

PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHE

DEFAULTCELL_FLASH_CACHEDEFAULT)

TABLESPACE"NEWCCS"

SQL>

从上面的查询可以看到，返回的结果里面含有一些storage属性，看起来很不舒服。我们可以通过设置会话级别来不显示这些storage属性。

Example:FetchtheDDLforallCompleteTablesintheCurrentSchema,FilterOutNestedTablesandOverflowSegments

ThisexamplefetchestheDDLforall"complete"tablesinthecurrentschema,filteringoutnestedtablesandoverflowsegments.TheexampleusesSET_TRANSFORM_PARAM(withthehandlevalue=DBMS_METADATA.SESSION_TRANSFORMmeaning"forthecurrentsession")tospecifythatstorageclausesarenottobereturnedintheSQLDDL.Afterwards,theexampleresetsthesession-levelparameterstotheirdefaults.

Togeneratecomplete,uninterruptedoutput,setthePAGESIZEto0andsetLONGtosomelargenumber,asshown,beforeexecutingyourquery.

SQL>SETLONG2000000

SQL>SETPAGESIZE0

SQL>EXECUTEDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'

STORAGE',false);

PL/SQL过程已成功完成。

SQL>selectdbms_metadata.get_ddl('TABLE','BBS','NEWCCS')fromdual;

CREATETABLE"NEWCCS"."BBS"

("BBSID"NUMBER(16,0),

"SENDER"VARCHAR2(20)NOTNULLENABLE,

"INCEPT"VARCHAR2(20)NOTNULLENABLE,

"MSGTITLE"VARCHAR2(40)NOTNULLENABLE,

"MSG"VARCHAR2(500),

"SENDTIME"DATEDEFAULTsysdateNOTNULLENABLE,

"MSGTOP"VARCHAR2(1)DEFAULT0,

"MSGFILENAME"VARCHAR2(100),

"MSGFILE"LONGRAW,

"EDITTIME"DATEDEFAULTsysdate,

CONSTRAINT"PK_BBS"PRIMARYKEY("BBSID")

USINGINDEXPCTFREE10INITRANS2MAXTRANS255COMPUTESTATISTICS

TABLESPACE"NEWCCS"ENABLE

)SEGMENTCREATIONIMMEDIATE

PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING

TABLESPACE"NEWCCS"

SQL>EXECUTEDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'

DEFAULT');

PL/SQL过程已成功完成。

SQL>

2.查看存储过程的定义语句

SQL>SETLONG9999

SQL>selectdbms_metadata.get_ddl('PROCEDURE','ANALYZEDB','NEWCCS')fromdual;

CREATEORREPLACEPROCEDURE"NEWCCS"."ANALYZEDB"

IS

CURSORget_ownertable

IS

SELECTtable_name

FROMuser_tables;

ownertableget_ownertable%ROWTYPE;

BEGIN

OPENget_ownertable;

LOOP

FETCHget_ownertable

INTOownertable;

EXITWHENget_ownertable%NOTFOUND;

EXECUTEIMMEDIATE'analyzetable'

||ownertable.table_name

||'computestatisticsfortableforallindexesforallindexedcolumns';

ENDLOOP;

EXCEPTION

WHENOTHERS

THEN

RAISE;

END;

其他的查询和这个类似，只需修改对象类型即可。感兴趣的自己实验一下。

------------------------------------------------------------------------------

Blog：http://blog.csdn.net/tianlesoftware

网上资源：http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1群：62697716(满);DBA2群：62697977

<!--EndFragment-->