有时候,我们想查看表,存储,触发器等对象的定语语句,有以下两种方法:
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