How to Obtain List of Objects Compiled With 'DEBUG' Option in the Database [ID 231349.1] |
In this Document
Goal
Solution
References
Applies to:
PL/SQL
Information in this document applies to any platform.
Most often, PL/SQL objects are compiled with the 'DEBUG' option to obtain debug information. But, once compiled, it is difficult
to ascertain whether debug information was collected for a particular object or not. This document helps you to find the list of
database objects which were compiled using the 'DEBUG' option.
Developer's or DBA's who need to find which of the database objects have been compiled using the debug option. This option
allows debug information to be generated during object compilation.
You can query the view SYS.ALL_PROBE_OBJECTS to obtain information whether the object has been compiled using the
debug option or not.
To query this view, you need to connect to the database as the SYS user or a user with the DBA role.
SQL> describe SYS.ALL_PROBE_OBJECTS
Name Null? Type
----------------- -------- ------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
DEBUGINFO VARCHAR2(1)
Check for the value in the column DEBUGINFO. If it contains 'T', it means that debug information has been collected during its
compilation. If it has 'F', then debug option was not used during its compilation.
The above view does not contain a public synonym, hence you will have to prefix the view name with the
SYS schema (SYS.ALL_PROBE_OBJECTS).
If using Oracle 9i, there is an additional view which provides the same information: USER_STORED_SETTINGS.
SQL> describe USER_STORED_SETTINGS
Name Null? Type
----------------------- -------- ----------------
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OBJECT_TYPE VARCHAR2(12)
PARAM_NAME NOT NULL VARCHAR2(30)
PARAM_VALUE VARCHAR2(4000)
In this view, the column PARAM_VALUE has values DEBUG or NON_DEBUG,
where PARAM_VAME is plsql_compiler_flags, to indicate whether debug option
was used or not during the object compilation.
Examples:
1) Procedure compiled without any DEBUG option:
SQL> create or replace procedure proc_nodebug as
eno number;
begin
select count(*) into eno from emp;
dbms_output.put_line('Number of entries in table - EMP is : '|| eno);
end;
SQL> alter procedure proc_nodebug compile;
SQL> select OBJECT_NAME, debuginfo from SYS.ALL_PROBE_OBJECTS
where object_name like '%PROC_NODEBUG%';
OBJECT_NAME DEBUGINFO
------------------------------ --------------------
PROC_NODEBUG F
SQL> col PARAM_VALUE format a22
SQL> select OBJECT_NAME,PARAM_NAME,PARAM_VALUE
from USER_STORED_SETTINGS where object_name like 'PROC_NODEBUG%';
OBJECT_NAME PARAM_NAME PARAM_VALUE
--------------- ------------------------------ ----------------------
PROC_NODEBUG plsql_compiler_flags INTERPRETED,NON_DEBUG
PROC_NODEBUG nls_length_semantics BYTE
If no option is specified while compiling the procedure, the default is NODEBUG.
2) Procedure compiled with DEBUG option :
SQL> create or replace procedure proc_debug as
eno number;
begin
select count(*) into eno from emp;
dbms_output.put_line('Number of entries in table - EMP is : '|| eno);
end;
SQL> alter procedure proc_debug compile debug;
SQL> select * from SYS.ALL_PROBE_OBJECTS where object_name like 'PROC_DEBUG%';
OBJECT_NAME DEBUGINFO
------------------------------ --------------------
PROC_DEBUG T
SQL> col PARAM_VALUE format a20
SQL> select OBJECT_NAME,PARAM_NAME , PARAM_VALUE from USER_STORED_SETTINGS
where object_name like 'PROC_DEBUG%';
OBJECT_NAME PARAM_NAME PARAM_VALUE
------------------------------ ------------------------------ --------------
PROC_DEBUG plsql_compiler_flags DEBUG
PROC_DEBUG nls_length_semantics BYTE
NOTE:151224.1 - PL/SQL Native Compilation in Oracle9i
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-2146455/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25583515/viewspace-2146455/