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;

 

其他的查詢和這個類似，只需修改物件類型即可。 感興趣的自己實驗一下。

 

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

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/tianlesoftware/archive/2010/06/19/5679293.aspx