experimenting with dbms_sqldiag.dump_trace

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 2 Oct 2013 09:51:29 -0500
Message-ID: <CAPZQniUWJ1XQLhtZ_GMswn8O92svq2eMR2Zw9zW2jNBi8qAyQg_at_mail.gmail.com>



Good day, listers,
At a recent OakTable session with the glowing Scotsman Doug Burns (*grin*), I was introduced to dbms_sqldiag.dump_trace (who further gave credit back to Maria Colgan and Greg Rahn). Since this is *STILL* undocumented, I am curious why certain components can be explicitly traced, while others cannot. For instance (from a OSEE 11.2.0.2, 11.2.0.4 or 12.1.0.1 database):

SQL > exec dbms_sqldiag.dump_trace ('fq0whgg02000r',0,'APA','APA_trace'); BEGIN dbms_sqldiag.dump_trace ('fq0whgg02000r',0,'APA','APA_trace'); END;

*
ERROR at line 1:

ORA-20001: Invalid value for component parameter
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1190
ORA-06512: at line 1


SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Semantic','Semantic_trace');
BEGIN dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Semantic','Semantic_trace'); END;

*
ERROR at line 1:

ORA-20001: Invalid value for component parameter
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1190
ORA-06512: at line 1


SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Optimizer','Optimizer_trace');

PL/SQL procedure successfully completed.

SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Compiler','Compiler_trace');

PL/SQL procedure successfully completed.

-- 
Charles Schultz


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 02 2013 - 16:51:29 CEST

Original text of this message