PL/SQL相關的資料字典

PL/SQL相關的資料字典
http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62plsql-1851968.html

有時候，我們在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對很多
檢視提供了使用者介面，從而更容易的瀏覽它們的內容。