In this Document
  Goal
  Solution
  References

Applies to:

PL/SQL
Information in this document applies to any platform.

Goal

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.

Solution

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

References

NOTE:151224.1 - PL/SQL Native Compilation in Oracle9i