PL/SQL相關的資料字典

PL/SQL相關的資料字典

有時候，我們在PL/SQL開發過程中會遇到以下問題：
1）我的程式到底依賴於哪些資料庫物件？
2）哪個包中調用了一個其他包中的子程式或變數？
3）我的哪個子程式的參數使用了不合適的資料類型？
4）我的所有子程式是否都使用了適當的最佳化層級？

傻一點的做法是到代碼裡搜。。。
聰明的人會使用以下資料字典視圖：

【USER_ARGUMENTS】：包含某模式中所有過程和函數的參數。
【USER_DEPENDENCIES】：包含你擁有的對象間依賴。這個視圖最常被Oracle用來標識失效的資料庫物件（當該對象依賴的對象發生改變後）。
【USER_ERRORS】：包含你擁有的所有已儲存的對象的編譯錯誤。這個視圖可被SQL*Plus命令：SHOW ERRORS讀取。
【USER_IDENTIFIERS】：11g引入並由PL/Scope編譯工具填充。一旦填充，這個視圖將包含所有標識符-程式名稱，變數等。
【USER_OBJECT_SIZE】：包含對象的大小。事實上，這個視圖展示了原始，解析和編譯的代碼大小。儘管是被編譯器和運行時殷勤使用，你也可以用來找到環境中的大程式。
【USER_OBJECTS】：包含一個模式下所有對象資訊。可查看對象是否valid，找出所有包含EMP名稱的包等。
【USER_PLSQL_OBJECT_SETTINGS】：Information about the characteristics—such as the optimization level and debug settings—of a PL/SQL object that can be modified through the ALTER and SET DDL commands.
【USER_PROCEDURES】： 包含關於儲存程式資訊，例如AUTHID設定，程式是定義為DETERMINISTIC等特性。
【USER_SOURCE】：包含所有對象的文本原始碼。一個非常便利的視圖，因為你可以對原始碼進行各種分析。
【USER_STORED_SETTINGS】：PL/SQL編譯器標記。使用這個視圖查看哪個程式用了原生編譯（native compilation）。
【USER_TRIGGERS】 和【USER_TRIGGER_COLS】：The database triggers you own (including the source code and a description of the triggering event) and any columns identified with the triggers, respectively. You can write programs against USER_TRIGGERS to enable or disable triggers for a particular table.

1、資料字典基礎
資料字典是由多個執行個體建立的表和視圖組成，使用者通常只有對資料字典查詢許可權。
絕大多數的資料欄位有三個版本組成：
1）使用者視圖，USER_開頭，包含當前已登入使用者擁有的資料庫物件資訊。
2）全部視圖，ALL_開頭，包含當前已登入使用者已讀取的資料庫物件資訊。
3）管理員視圖，DBA_開頭，這類別檢視包含一個執行個體中所有資料庫物件資訊，普通使用者通常無權訪問。

例如：
SELECT * FROM user_objects; –我擁有的所有資料庫物件資訊
SELECT * FROM all_objects; –我有權讀取的資料庫物件資訊
SELECT * FROM dba_objects; –管理員可訪問的整個資料庫的對象資訊

2、顯示儲存物件的資訊 USER_OBJECTS
包含列介紹（英文太簡單不翻譯了）：
OBJECT_NAME: Name of the object

OBJECT_TYPE: Type of the object, such as PACKAGE, FUNCTION, or TRIGGER

STATUS: Status of the object—VALID or INVALID

LAST_DDL_TIME: Time stamp indicating the last time this object was changed

來看幾個例子：

1）顯示我模式下所有表:

SELECT object_name FROM user_objects WHERE object_type = 'TABLE' ORDER BY object_name; 

2）顯示所有失效的對象名：

SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID' ORDER BY object_type, object_name; 

3）顯示所有今天修改的對象：

SELECT object_type, object_name, last_ddl_time FROM user_objects WHERE last_ddl_time >= TRUNC (SYSDATE) ORDER BY object_type, object_name 

3、搜尋和展現原始碼 USER_SOURCE
列介紹：
NAME: Name of the object

TYPE: Type of the object (ranging from PL/SQL program units to Java source and trigger source)

LINE: Number of the line of the source code

TEXT: Text of the source code

例如： 我需要改變包SALES_MGR中CALC_TOTALS過程的參數列表。我想找到哪些地方對該過程進行了調用。

SELECT name, line, text FROM user_source WHERE UPPER (text) LIKE '%SALES_MGR.CALC_TOTALS%' ORDER BY name, line 

當然，這個查詢可能連注釋也查出來，還有就是不符合LIKE格式的字串將無法檢索出來，例如：
SALES_MGR.
CALC_TOTALS

那麼假設，我們的代碼都是比較標準的，這個查詢還是做了一個不錯的工作。
另外，對於11g而言，你可以使用PL/Scope特性。

4、儲存代碼的編譯設定 USER_PLSQL_OBJECT_SETTINGS
PLSQL_OPTIMIZE_LEVEL: 編譯對象的最佳化層級

PLSQL_CODE_TYPE: 對象的編譯模式

PLSQL_DEBUG: Whether or not the object was compiled for debugging 對象是否為調試而編譯

PLSQL_WARNINGS: 編譯對象的編譯警告設定

NLS_LENGTH_SEMANTICS: NLS length semantics that were used to compile the object 編譯對象的語義長度設定

找出所有沒有採用有效編譯時間最佳化的程式單元：

SELECT name
FROM user_plsql_object_settings
WHERE plsql_optimize_level < 2

0級表示未採取任何最佳化。1表示最低限度的最佳化。2者都不應該存在於生產環境。

找出那些禁用了編譯時間警告的程式。

SELECT name, plsql_warnings FROM user_plsql_object_settings WHERE plsql_warnings LIKE '%DISABLE%'; 

5、關於過程和函數的詳細資料 USER_PROCEDURES
AUTHID: Shows whether a procedure or a function is defined as an invoker rights (CURRENT_USER) or definer rights (DEFINER) program unit 調用者許可權或是定義者許可權

DETERMINISTIC: Set to YES if the function is defined to be deterministic, which theoretically means that the value returned by the function is determined completely by the function’s argument values 是否確定性

PIPELINED: Set to YES if the function is defined as a pipelined function, which means that it can be executed in parallel as part of a parallel query 是否管道函數

OVERLOAD: Set to a positive number if this subprogram is overloaded, which means that there are at least two subprograms with this name in the same package 是否重載

找出所有運行在調用者許可權下的過程和函數

SELECT object_name , procedure_name FROM user_procedures WHERE authid = 'CURRENT_USER' ORDER BY object_name, procedure_name 

顯示所有聲明為確定性函數：

SELECT object_name , procedure_name FROM user_procedures WHERE deterministic = 'YES' ORDER BY object_name, procedure_name 

6、分析和修改觸發器狀態 USER_TRIGGERS
TRIGGER_NAME: The name of the trigger

TRIGGER_TYPE: A string that shows if this is a BEFORE or AFTER trigger and whether it is a row- or statement-level trigger (in a trigger that is fired before an INSERT statement, for example, the value of this column is BEFORE STATEMENT)

TRIGGERING_EVENT: The type of SQL operation—such as INSERT, INSERT OR UPDATE, DELETE OR UPDATE—that will cause the trigger to fire

TABLE_NAME: The name of the table on which the trigger is defined

STATUS: The status of the trigger—ENABLED or DISABLED

WHEN_CLAUSE: An optional clause you can use to avoid unnecessary execution of the trigger body

TRIGGER_BODY: The code executed when the trigger fires

找出所有已禁用的觸發器:

SELECT * FROM user_triggers WHERE status = 'DISABLED' 

找出所有定義在EMPLOYEES表上的行級觸發器：

SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES' AND trigger_type LIKE '%EACH ROW' 

Find all triggers that fire when an UPDATE operation is performed:
找出所有包含update操作觸發的觸發器

SELECT * FROM user_triggers WHERE triggering_event LIKE '%UPDATE%' 

7、對象依賴分析 USER_DEPENDENCIES
NAME: Name of the object

TYPE: Type of the object

REFERENCED_OWNER: Owner of the referenced object 被引用對象的所有者

REFERENCED_NAME: Name of the referenced object 被引用對象的名稱

REFERENCED_TYPE: Type of the referenced object 被引用對象的類型

找出所有依賴於EMPLOYEES表的對象：

SELECT type, name FROM user_dependencies WHERE referenced_name = 'EMPLOYEES' ORDER BY type, name 

找出當前模式下ORDER_MGR包依賴的所有對象

SELECT referenced_type , referenced_name FROM user_dependencies WHERE name = 'ORDER_MGR' AND referenced_owner = USER ORDER BY referenced_type, referenced_name 

8、分析參數資訊 USER_ARGUMENTS
OBJECT_NAME: The name of the procedure or function

PACKAGE_NAME: The name of the package in which the procedure or function is defined

ARGUMENT_NAME: The name of the argument

POSITION: The position of the argument in the parameter list (if 0, this is the RETURN clause of a function)

IN_OUT: The mode of the argument—IN, OUT, or IN OUT

DATA_TYPE: The datatype of the argument

DATA_LEVEL: The nesting depth of the argument for composite types (for example, if one of your arguments’ datatypes is a record, USER_ARGUMENTS will have a row for this argument with a DATA_LEVEL of 0 and then a row for each field in the record with a DATA_LEVEL of 1)

1）找出所有包含LONG參數的程式

SELECT object_name , package_name , argument_name FROM user_arguments WHERE data_type = 'LONG' ; 

2）找出所有帶有OUT或IN OUT參數的函數。這個地方要注意一下：有經驗的編程專家都會告訴我們不要在函數中僅使用IN 參數，
原因是帶有OUT 和 IN OUT參數函數不能在SQL中被調用，並且不能用在函數索引中。如果你需要函數返回多塊資訊，那麼請使用
一個預存程序或返回一個RECORD類型。下面的例子找出了違反這個條件的函數：

SELECT ua.object_name, 2 ua.package_name, 3 ua.argument_name, 4 ua.in_out 5 FROM (SELECT * 6 FROM user_arguments 7 WHERE position = 0) funcs, 8 user_arguments ua 9 WHERE ua.in_out IN ('OUT', 'IN OUT') 10 AND ua.position > 0 11 AND ua.data_level = 0 12 AND funcs.object_name = ua.object_name 13 AND funcs.package_name = ua.package_name 14 AND ( funcs.overload = ua.overload 15 OR (funcs.overload IS NULL 16 AND ua.overload IS NULL)); 

9 總結：這兒有座金礦
This article merely scratches the surface of the application information that can be mined from the data dictionary views in Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to many of these views, making it easier to browse their contents.

本文只是拂去了可以從Oracle資料字典視圖中挖掘出的應用資訊的一層表面。PL/SQL編輯器例如Oracle SQL Developer對很多 視圖提供了使用者介面，從而更容易的瀏覽它們的內容。

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享