Oracle 查看 表 存储过程 触发器 函数 等对象定义语句的方法
有时候，我们想查看表，存储，触发器等对象的定语语句，有以下两种方法：
1. 查 all_source 表
2. 用 DBMS_METADATA 包。
一． 通过 all_source 表
先来确认下，通过all_source 表可以查看哪些类型的对象：
SQL> SELECT distinct type FROM ALL_SOURCE;
TYPE
------------
PROCEDURE
PACKAGE
PACKAGE BODY
LIBRARY
TYPE BODY
TRIGGER
FUNCTION
JAVA SOURCE
TYPE
从以上的结果我们可以看到，我们可以通过该表查询的对象.
查看存储过程定义语句：
SQL> SELECT text FROM ALL_SOURCE where TYPE='PROCEDURE' AND NAME ='ADDCUSTBUSS';
TEXT
--------------------------------------------------------------------------------
PROCEDURE addcustbuss (
acustid IN custbuss.custid%TYPE,
bussname IN custbuss.businessname%TYPE,
aopid IN custbuss.opid%TYPE,
acreatetime IN custbuss.createtime%TYPE,
aCustTel IN custbuss.CustTel%TYPE,--客户电话
aContact IN custbuss.Contact%TYPE,--联系人
aFeedback IN custbuss.Feedback%TYPE,--客户反馈
asid OUT custbuss.ID%TYPE,
RESULT OUT INTEGER
)
IS
BEGIN
RESULT := -1;
SELECT getarea || TO_CHAR (idseq.NEXTVAL, 'FM0999999999')
INTO asid
FROM DUAL;
INSERT INTO custbuss
(ID, custid, businessname, opid, createtime,CustTel,Contact,Feedback
)
VALUES (asid, acustid, bussname, aopid, acreatetime,aCustTel,aContact,aFeedback
);
RESULT := 0;
EXCEPTION
WHEN OTHERS
THEN
RESULT := -1;
END addcustbuss;
已选择32行。
SQL>
查看触发器 定义语句
SQL> SELECT text FROM ALL_SOURCE where TYPE='TRIGGER' AND NAME ='TRDB_TEAM';
TEXT
-----------------------------------------------------------------------------
TRIGGER "NEWCCS".trdb_team
BEFORE DELETE
ON team
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
DELETE FROM team_teamgroup_map
WHERE teamid = :OLD.teamid;
END trdb_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> select dbms_metadata.get_ddl('对象类型','名称','用户名') from dual;
1. 查看表的定义语句
SQL> set long 9999999
SQL> select dbms_metadata.get_ddl('TABLE','BBS','NEWCCS') from dual;
DBMS_METADATA.GET_DDL('TABLE','BBS','NEWCCS')
--------------------------------------------------------------------------------
CREATE TABLE "NEWCCS"."BBS"
( "BBSID" NUMBER(16,0),
"SENDER" VARCHAR2(20) NOT NULL ENABLE,
"INCEPT" VARCHAR2(20) NOT NULL ENABLE,
"MSGTITLE" VARCHAR2(40) NOT NULL ENABLE,
"MSG" VARCHAR2(500),
"SENDTIME" DATE DEFAULT sysdate NOT NULL ENABLE,
"MSGTOP" VARCHAR2(1) DEFAULT 0,
"MSGFILENAME" VARCHAR2(100),
"MSGFILE" LONG RAW,
"EDITTIME" DATE DEFAULT sysdate,
CONSTRAINT "PK_BBS" PRIMARY KEY ("BBSID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "NEWCCS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "NEWCCS"
SQL>
从上面的查询可以看到，返回的结果里面含有一些storage 属性，看起来很不舒服。我们可以通过设置会话级别来不显示这些storage 属性。
Example: Fetch the DDL for all Complete Tables in the Current Schema, Filter Out Nested Tables and Overflow Segments
This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults.
To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.
SQL> SET LONG 2000000
SQL> SET PAGESIZE 0
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'
STORAGE',false);
PL/SQL 过程已成功完成。
SQL> select dbms_metadata.get_ddl('TABLE','BBS','NEWCCS') from dual;
CREATE TABLE "NEWCCS"."BBS"
( "BBSID" NUMBER(16,0),
"SENDER" VARCHAR2(20) NOT NULL ENABLE,
"INCEPT" VARCHAR2(20) NOT NULL ENABLE,
"MSGTITLE" VARCHAR2(40) NOT NULL ENABLE,
"MSG" VARCHAR2(500),
"SENDTIME" DATE DEFAULT sysdate NOT NULL ENABLE,
"MSGTOP" VARCHAR2(1) DEFAULT 0,
"MSGFILENAME" VARCHAR2(100),
"MSGFILE" LONG RAW,
"EDITTIME" DATE DEFAULT sysdate,
CONSTRAINT "PK_BBS" PRIMARY KEY ("BBSID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "NEWCCS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "NEWCCS"
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'
DEFAULT');
PL/SQL 过程已成功完成。
SQL>
2. 查看 存储过程 的定义语句
SQL> SET LONG 9999
SQL> select dbms_metadata.get_ddl('PROCEDURE','ANALYZEDB','NEWCCS') from dual;
CREATE OR REPLACE PROCEDURE "NEWCCS"."ANALYZEDB"
IS
CURSOR get_ownertable
IS
SELECT table_name
FROM user_tables;
ownertable get_ownertable%ROWTYPE;
BEGIN
OPEN get_ownertable;
LOOP
FETCH get_ownertable
INTO ownertable;
EXIT WHEN get_ownertable%NOTFOUND;
EXECUTE IMMEDIATE 'analyze table '
|| ownertable.table_name
|| ' compute statistics for table for all indexes for all indexed columns ';
END LOOP;
EXCEPTION
WHEN OTHERS
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
