2

How can I get list of stored procedures/Function which are compiled for debug?

I do not see relevant column in following tables.

  • DBA_PROCEDURES
  • DBA_OBJECTS
5

I think DBA_PLSQL_OBJECT_SETTINGS gives more appropriate information.

  • What is difference? Does it give me more information? – Atilla Ozgur Mar 18 '14 at 12:33
  • it not only tells you if it has been debug enabled, also gives you special compiler flags used (if any). – Raj Mar 18 '14 at 12:34
3

Here is the documented way:

ALL_PLSQL_OBJECT_SETTINGS view, PLSQL_DEBUG column

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn20385.htm#REFRN20385

2

Look in SYS.ALL_PROBE_OBJECTS. The DEBUGINFO column contains a T (true) or F (false) indicating whether it was compiled in debug mode.

2

Thanks for Phil's answer, following query do what I need, compile all debug compiled objects.

SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name || '     compile;'
FROM   SYS.ALL_PROBE_OBJECTS
WHERE DEBUGINFO = 'T'
ORDER BY owner, object_type, object_name;
1

just for future supplementary info

SELECT PO.OWNER,PO.OBJECT_NAME,PO.OBJECT_TYPE,PO.DEBUGINFO
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE BODY;',' COMPILE;') COPILE_NO_DEBUG
,'ALTER ' || REPLACE(object_type,'PACKAGE BODY','PACKAGE') || ' ' || owner || '.' || object_name ||DECODE(object_type,'PACKAGE BODY',' COMPILE DEBUG BODY;',' COMPILE DEBUG;') COPILE_WITH_DEBUG
FROM   SYS.ALL_PROBE_OBJECTS PO
--WHERE OBJECT_NAME='YOUR_DEFINITION_PKG' AND DEBUGINFO IN ('F','T')
ORDER BY owner, object_type, object_name;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.