[SCRIPT] - trace_10046.sql

  • 14/10/2016/
  • Performance, Diagnostics & Tuning

--
--
-- SCRIPT : trace_10046.sql
-- AUTHOR : Carlos Furushima
-- 
-- PURPOSE : Gera Trace tipo 10046, onde a saida do mesmo informa onde este esta localizado.
--
-- Instruções para uso :
-- Copie o script SQL abaixo, salvando em seu Sistema Operacional, utilizando o nome trace_10046.sql, para que o mesmo seja executado no SQLPLUS.
-- 

-- SQL> @trace_10046.sql
-- Enter value for sid: 482
--
----------------------------
-- SID => 482
-- SERIAL => 52631
-- Trace File => /u/app/oracle/diag/rdbms/pback/pback1/trace/pback1_ora_4947.trc
----------------------------
--
--

 


set serveroutput on size unlimited
--------

 

declare 
V_sid number;
      V_serial number;
      V_tfile VARCHAR2(200);
      trace10048 VARCHAR2(2000);

 

begin
      V_sid := &sid;
SELECT serial# into V_serial  
FROM  v$session s  
WHERE  s.sid = V_sid ;


 

-----------------
-- Detalhes dos niveis (4 argumento da procedure DBMS_SYSTEM.set_ev)
-- 0 : Desligar trace (parar rastreamento)
-- 2 : Trace File : Nivel basico
-- 4 : Trace File : Nivel Basico + bind variable
-- 8 : Trace File : Nivel Basico + wait events
-- 12 : Trace File : Nivel Basico + bind variable + wait events
-----------------

 

-- Start Trace 10046 (nivel = 12)
DBMS_SYSTEM.set_ev(V_sid,V_serial,10046,12,'')  ;
-- Stop Trace 10046 (nivel = 12)
-- DBMS_SYSTEM.set_ev(V_sid,V_serial,10046,0,'')  ;

 

SELECT p.tracefile into V_tfile 
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = V_sid ;

 

   dbms_output.put_line ('                   ');
   dbms_output.put_line ('----------------------------');
   dbms_output.put_line ('SID => '|| V_sid );
   dbms_output.put_line ('SERIAL => '|| V_serial );
   dbms_output.put_line ('Trace File => '|| V_TFILE );
   dbms_output.put_line ('----------------------------');
   dbms_output.put_line ('                   ');
end;
/

 

-----

-- Referencia : 
-- http://www.juliandyke.com/Diagnostics/Trace/EnablingTrace.php
-- http://shahiddba.blogspot.com.br/2012/12/tracefileidentifier.html
-- http://oracleappstechnology.blogspot.com.br/2007/07/activate-extended-sql-trace-for-given.html
-- http://www.gplivna.eu/papers/otrace.htm
-- https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof
-- http://www.petefinnigan.com/ramblings/how_to_set_trace.htm