Oracle檢視錶、儲存過程、觸發器、函式等物件定義語句

檢視錶、儲存過程、觸發器等物件的定義語句有兩種方法：

1、查ALL_SOURCE表

2、用DBMS_METADATA包

 

一、通過ALL_SOURCE表

先來確認一下，通過ALL_SOURCE表可以檢視哪些型別的物件：

SQL> select distinct type from ALL_SOURCE;

 

TYPE

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

PROCEDURE

PACKAGE

PACKAGE BODY

TYPE BODY

TRIGGER

FUNCTION

TYPE

 

已選擇7行。

 

 

這些就是可以通過ALL_SOURCE表查到的物件。

 

使用方法：

SQL> SELECT  text FROM ALL_SOURCE where TYPE=’PROCEDURE’  AND NAME=’ADDCUSTBUSS’;

只需修改TYPE和NAME為要查詢的型別和名稱即可。注意大寫！

 

二、通過DBMS_METADATA包

通過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"

 

返回的結果裡面含有一些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 過程已成功完成。

 

 

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;

其他的查詢和這個類似，只需修改物件型別即可。