Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select name,type,plscope_settings from user_plsql_object_settings;
NAME TYPE PLSCOPE_SETTINGS
------------------------------ ------------ --------------------------------------------------------------------------------
AWR_RPT PROCEDURE IDENTIFIERS:NONE
COLORLIST TYPE IDENTIFIERS:NONE
COLOR_HEX_CODE TYPE IDENTIFIERS:NONE
COLOR_TYPE TYPE IDENTIFIERS:NONE
COURSELIST TYPE IDENTIFIERS:NONE
DEAD_CODE PROCEDURE IDENTIFIERS:NONE
INSERT_EMP PROCEDURE IDENTIFIERS:NONE
MANAGER TYPE IDENTIFIERS:NONE
PLCH_NUMBER_NT TYPE IDENTIFIERS:NONE
PLCH_PKG PACKAGE IDENTIFIERS:NONE
PLCH_PKG PACKAGE BODY IDENTIFIERS:NONE
PROC PROCEDURE IDENTIFIERS:NONE
PROJECTLIST TYPE IDENTIFIERS:NONE
P_A PROCEDURE IDENTIFIERS:NONE
P_DEAL PROCEDURE IDENTIFIERS:NONE
P_EXP_USER_OBJECTS PROCEDURE IDENTIFIERS:NONE
P_SCOTT_UNDO PROCEDURE IDENTIFIERS:NONE
P_TEST PROCEDURE IDENTIFIERS:NONE
P_TIMESTAT PROCEDURE IDENTIFIERS:NONE
RESOLVE_STR TYPE IDENTIFIERS:NONE
NAME TYPE PLSCOPE_SETTINGS
------------------------------ ------------ --------------------------------------------------------------------------------
SHOW_SPACE PROCEDURE IDENTIFIERS:NONE
SYS_PLSQL_75043_18_1 TYPE IDENTIFIERS:NONE
SYS_PLSQL_75043_DUMMY_1 TYPE IDENTIFIERS:NONE
XPLAN_NTT TYPE IDENTIFIERS:NONE
XPLAN_OT TYPE IDENTIFIERS:NONE
25 rows selected
11g中默认情况下程序的plscope_settings设置为IDENTIFIERS：NONE。
要追踪标识符的详细信息，需要在编译之前对会话或者程序进行plscope_settings的设置。
SQL> alter session set plscope_settings='IDENTIFIERS:ALL';
Session altered
SQL> desc user_identifiers
Name Type Nullable Default Comments
---------------- ------------ -------- ------- ------------------------------------------------------
NAME VARCHAR2(30) Y Name of the identifier
SIGNATURE VARCHAR2(32) Y Signature of the identifier
TYPE VARCHAR2(18) Y Type of the identifier
OBJECT_NAME VARCHAR2(30) Name of the object where the identifier usage occurred
OBJECT_TYPE VARCHAR2(13) Y Type of the object where the identifier usage occurred
USAGE VARCHAR2(11) Y Type of the identifier usage
USAGE_ID NUMBER Y Unique key for an identifier usage within the object
LINE NUMBER Y Line number of the identifier usage
COL NUMBER Y Column number of the identifier usage
USAGE_CONTEXT_ID NUMBER Y Context USAGE_ID of an identifier usage
SQL>
SQL> create or replace package pck_emp is
2
3 -- Author : TIAN
4 -- Created : 2013-7-10 10:47:11
5 -- Purpose :
6
7 -- Public type declarations
8 procedure emp(e_name in out varchar2) ;
9
10 end ;
11 /
Package created
SQL>
SQL> create or replace package body pck_emp is
2
3 procedure emp(e_name in out varchar2)
4 is
5 e_name2 varchar2(10);
6 begin
7 select ename into e_name2 from emp
8 where sal<(select sal from emp where ename=e_name)
9 and rownum=1;
10 e_name:=e_name2;
11 end;
12
13 end ;
14 /
Package body created
SQL> select * from sys.user_identifiers t;
NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID
---------- -------------------------------- ------------------ ------------ ------------- ----------- ---------- ---------- ---------- ----------------
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE REFERENCE 4 8 31 3
E_NAME EEC680F737C81AC6FCE099599CFE8EA5 FORMAL IN OUT PCK_EMP PACKAGE DECLARATION 3 8 17 2
EMP 639B48ACE35ABA0B34166F9D0F380AE9 PROCEDURE PCK_EMP PACKAGE DECLARATION 2 8 13 1
PCK_EMP 0769B61A5B5AE1A77D87F29181DFB354 PACKAGE PCK_EMP PACKAGE DECLARATION 1 1 9 0
E_NAME2 28300FF547247DEE8135AF1551055F60 VARIABLE PCK_EMP PACKAGE BODY REFERENCE 11 10 13 10
E_NAME DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT PCK_EMP PACKAGE BODY ASSIGNMENT 10 10 5 2
E_NAME2 28300FF547247DEE8135AF1551055F60 VARIABLE PCK_EMP PACKAGE BODY ASSIGNMENT 9 7 23 2
ROWNUM 65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION PCK_EMP PACKAGE BODY CALL 8 9 9 2
E_NAME DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT PCK_EMP PACKAGE BODY REFERENCE 7 8 48 2
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 6 5 13 5
E_NAME2 28300FF547247DEE8135AF1551055F60 VARIABLE PCK_EMP PACKAGE BODY DECLARATION 5 5 5 2
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 4 3 31 3
E_NAME DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT PCK_EMP PACKAGE BODY DECLARATION 3 3 17 2
EMP 639B48ACE35ABA0B34166F9D0F380AE9 PROCEDURE PCK_EMP PACKAGE BODY DEFINITION 2 3 13 1
PCK_EMP 0769B61A5B5AE1A77D87F29181DFB354 PACKAGE PCK_EMP PACKAGE BODY DEFINITION 1 1 14 0
15 rows selected
所有标示符的详细信息都被记录下来了。该视图中的内容不会重复，多次编译一个程序，产生的结果集是最后编译生成的结果。
如果将上面的pck_emp包删除，该视图中的内容也一并被删掉了。
通过该视图，我们可以进行命名规范的审核。
比如，规范中约定“所有输入输出参数的命名格式为IO_
我们就可以找到此视图中TYPE=‘FORMAL IN OUT’的列，检查NAME是否是“IO_”开头，将不符合该条件的列显示出来。
作为数据库管理员，可以将这些信息详细地反馈给开发人员，方便进行修改。
该视图将包级别、存储过程及函数（子程序）级别、局部变量和参数级别的内容显示在一起，
可以通过usage_id和usage_context_id作为连接条件，将结果关联起来。
如：
SQL> select distinct level,type,name from user_identifiers
2 where object_type='PACKAGE'
3 start with usage_context_id=0
4 connect by usage_context_id=prior usage_id
5 order by level;
LEVEL TYPE NAME
---------- ------------------ ------------------------------
1 PACKAGE PCK_EMP
2 PROCEDURE EMP
3 FORMAL IN OUT E_NAME
4 CHARACTER DATATYPE VARCHAR2
在另外一个会话中编译：
create or replace package pck_emp is
-- Author : TIAN
-- Created : 2013-7-10 10:47:11
-- Purpose :
-- Public type declarations
procedure emp(e_name in out varchar2) ;
procedure emp2(e_name in out varchar2) ;
end ;
再回到当前会话，编译：
SQL> create or replace package body pck_emp is
2
3 procedure emp(e_name in out varchar2)
4 is
5 e_name2 varchar2(10);
6 begin
7 select ename into e_name2 from emp
8 where sal<(select sal from emp where ename=e_name)
9 and rownum=1;
10 e_name:=e_name2;
11 end;
12
13 procedure emp2(e_name in out varchar2)
14 is
15 e_name2 varchar2(10);
16 begin
17 select ename into e_name2 from emp
18 where sal<(select sal from emp where ename=e_name)
19 and rownum=1;
20 e_name:=e_name2;
21 end;
22 end ;
23 /
Package body created
再次查看user_identifiers，发现包声明中的相关信息消失了。
SQL> select * from sys.user_identifiers t;
NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID
------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------- ---------- ---------- ----------------
E_NAME2 19F69C47278618C3FBA2E509FF6ED130 VARIABLE PCK_EMP PACKAGE BODY REFERENCE 18 20 13 17
E_NAME 3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT PCK_EMP PACKAGE BODY ASSIGNMENT 17 20 5 0
E_NAME2 19F69C47278618C3FBA2E509FF6ED130 VARIABLE PCK_EMP PACKAGE BODY ASSIGNMENT 16 17 23 0
ROWNUM 65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION PCK_EMP PACKAGE BODY CALL 15 19 9 0
E_NAME 3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT PCK_EMP PACKAGE BODY REFERENCE 14 18 48 0
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 13 15 13 12
E_NAME2 19F69C47278618C3FBA2E509FF6ED130 VARIABLE PCK_EMP PACKAGE BODY DECLARATION 12 15 5 0
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 11 13 33 10
E_NAME 3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT PCK_EMP PACKAGE BODY DECLARATION 10 13 19 0
E_NAME2 AA38A15360CD117AAE623EEAFBCE5871 VARIABLE PCK_EMP PACKAGE BODY REFERENCE 9 10 13 8
E_NAME BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT PCK_EMP PACKAGE BODY ASSIGNMENT 8 10 5 0
E_NAME2 AA38A15360CD117AAE623EEAFBCE5871 VARIABLE PCK_EMP PACKAGE BODY ASSIGNMENT 7 7 23 0
ROWNUM 65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION PCK_EMP PACKAGE BODY CALL 6 9 9 0
E_NAME BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT PCK_EMP PACKAGE BODY REFERENCE 5 8 48 0
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 4 5 13 3
E_NAME2 AA38A15360CD117AAE623EEAFBCE5871 VARIABLE PCK_EMP PACKAGE BODY DECLARATION 3 5 5 0
VARCHAR2 FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP PACKAGE BODY REFERENCE 2 3 31 1
E_NAME BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT PCK_EMP PACKAGE BODY DECLARATION 1 3 17 0
18 rows selected
SQL>
实验结束后，将会话关闭或将plscope_settings设置修改回默认：
SQL> alter session set plscope_settings='IDENTIFIERS:NONE';
Session altered
要设置所有源代码编译时都记录标识符详细信息，可以在系统级别设置：
开启：
SQL> alter system set plscope_settings='IDENTIFIERS:ALL';
System altered
关闭：
SQL> alter system set plscope_settings='IDENTIFIERS:NONE';
System altered
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-768391/ ，如需转载，请注明出处，否则将追究法律责任。
