trace.sql

----------------------------------
-- SCRIPT  trace.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
set verify off

select b.username USR_DB,b.osuser USR_SO,b.machine MAQUINA ,b.program,b.server,
       b.SID,b.serial#,b.status,a.SPID SO_PROC, to_char(b.logon_time,'dd/mm/yyyy-hh24:mi') logon_em
  from v$process a,v$session b
   where a.addr = b.paddr
     and a.spid = &pid;


define p_sid=&sid
define p_serial=&serial

exec sys.dbms_system.set_bool_param_in_session(&p_sid,&p_serial,'timed_statistics',true)
exec sys.dbms_system.set_sql_trace_in_session(&p_sid,&p_serial,true)

prompt Trace habilitado.
accept x prompt "Pressione <ENTER> para desabilitar o Trace..."

exec sys.dbms_system.set_sql_trace_in_session(&p_sid,&p_serial,false)
exec sys.dbms_system.set_bool_param_in_session(&p_sid,&p_serial,'timed_statistics',false)

undefine sid
undefine p_sid
undefine p_serial
undefine serial
undefine x----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  trace_pack.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    trace_pack.sql
rem
rem FINALIDADE
rem    Cria a package TRACE_PACK e seu package body
rem
rem PARAMETROS
rem    nenhum
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set echo on time on timing on

-------------------------------------------------------------------*
--- Cria a especificacao da package
-------------------------------------------------------------------*
set termout off
create or replace
package TRACE_PACK
--
---------------------------------------------------------------------
--  OVERVIEW
--  
--  A package TRACE_PACK contem procedures e funcoes para:
--  * Ligar e desligar o trace em uma sessao
--  * Ler o arquivo de trace gerado por essa sessao
--
---------------------------------------------------------------------
--  HISTORICO
rem 14/05/2012 ALEXANDRE PIRES
--
------------
AS

--------------------------------------------------------------------
--  VARIAVEIS GLOBAIS DA PACKAGE
--
vNumLinhas    pls_integer := 0;       /* Numero de linhas ja lidas */

--------------------------------------------------------------------
--  PROCEDURES E FUNCTIONS
--

--------------------------------------------
procedure  LIGA_TRACE
           ( p_Rotina in varchar2 );
           ------------------------------------------------------
           --  FINALIDADE
           --  * Liga o trace da sessao corrente, e altera o
           --    valor de MODULE e CLIENT_INFO em V$SESSION
           ------------------------------------------------------
           --  PARAMETROS
           --  * p_Rotina =  Nome da rotina, presenta na coluna
           --               TLP_TAB_PROCESSO de TAB_LOG_PROCESSO
           --
--------------------------------------------
procedure  DESLIGA_TRACE
           ( p_Rotina in varchar2 );
           ------------------------------------------------------
           --  FINALIDADE
           --  * Desliga o trace da sessao corrente, e altera o
           --    valor de MODULE e CLIENT_INFO em V$SESSION
           ------------------------------------------------------
           --  PARAMETROS
           --  * p_Rotina =  Nome da rotina desligando o trace
           --
--------------------------------------------
function   GET_TRACE_LINE
           ( p_SPID in pls_integer )
           return VARCHAR2;
           ------------------------------------------------------
           --  FINALIDADE
           --  * Retorna a proxima linha do trace file.
           --  * Se o arquivo ainda nao foi aberto, abre o arquivo.
           --  * Se o arquivo ja' foi lido ate' o fim, retorna
           --    a string 'EOF'.
           ------------------------------------------------------
           --  PARAMETROS
           --  * p_Rotina =  Nome da rotina
           --
--------------------------------------------
procedure  CLOSE_TRACE_FILE;
           ------------------------------------------------------
           --  FINALIDADE
           --  * Fecha o trace file, e zera o contador de linhas.
           --  * Permite que GET_TRACE_LINE volte a ler o arquivo
           --    de trace do inicio.
           --
--------------------------------------------

end TRACE_PACK;
/
set termout on

show errors

-------------------------------------------------------------------*
--- Cria o corpo da package
-------------------------------------------------------------------*
set termout off
create or replace
package body TRACE_PACK as

--------------------------------------------------------------------
--  VARIAVEIS LOCAIS DO PACKAGE BODY
--

vWindows        pls_integer;            /* identifica se servidor usa Windows ou nao */
vTraceDir       v$parameter.value%type; /* diretorio do arquivo de trace */
vTraceName      varchar2(20);           /* nome real do arquivo de trace */
vTraceFileId    utl_file.file_type;     /* ID do arquivo de trace */
vEOF            boolean := FALSE;       /* identifica o fim do arquivo */
vText           varchar2(2000);         /* armazena o texto de uma linha do arquivo */
vSqlerrm        varchar2(2000);
eInvalidOper    exception;
type            typProgramaArray
                is table of VARCHAR2(10);
vProgramaArray  typProgramaArray := typProgramaArray(null);
vProgramaLista  VARCHAR2(64);           /* Mesmo type da v$session.client_info */

--------------------------------------------------------------------
--  PROCEDURES E FUNCTIONS LOCAIS DO PACKAGE BODY
--

/*------------------------------------------------------------------
||
|| FUNCTION ARQUIVOEXISTE
||
------------------------------------------------------------------*/
function        ARQUIVOEXISTE
                ( pDiretorio      varchar2
                , pArquivo        varchar2
                )
return boolean
---
is
   vArquivoId             utl_file.file_type;
---
begin
  vArquivoId := utl_file.fopen
                   ( location   => pDiretorio
                   , filename   => pArquivo
                   , open_mode  => 'r'
                   );
  utl_file.fclose( file => vArquivoId );
  return TRUE;
exception
when others then
  if utl_file.is_open( file => vArquivoId ) then
    utl_file.fclose( file => vArquivoId );
  end if;
  return FALSE;
end ARQUIVOEXISTE;


--------------------------------------------------------------------
--  PROCEDURES E FUNCTIONS PUBLICAS
--

/*------------------------------------------------------------------
||
|| PROCEDURE    LIGA_TRACE
||
------------------------------------------------------------------*/
procedure       LIGA_TRACE
                ( p_Rotina in varchar2 )
is
  ---
  pragma        AUTONOMOUS_TRANSACTION;
  ---
  vSPID         varchar2(9);
  vOsUser       varchar2(30);
  vDataExec     varchar2(30) := to_char(sysdate, 'yyyy-mm-dd_hh24:mi:ss');
  ---
  cursor c1 is  -- procura se rotina esta cadastrada na TAB_LOG_PROCESSO
         select tlp_tab_processo
           from tab_log_processo
          where tlp_tab_processo = p_Rotina
            and tlp_tab_tkprof   = 'S'
            for update;
---
begin

  -----------------------------------------
  --- Ajusta o ambiente para a V$SESSION
  -----------------------------------------
  dbms_application_info.set_module(p_Rotina, 'SQL_TRACE ligado');
  dbms_application_info.set_client_info(vOsUser || ' - ' || vDataExec);

  ---
  for r1 in c1 loop

     -----------------------------------------
     --- Procura SPID desta sessao
     -----------------------------------------
     select p.spid, s.osuser
       into vSPID, vOsUser
       from v$process p
          , v$session s
      where p.addr   = s.paddr
        and s.audsid = userenv('sessionid');

     -----------------------------------------
     --- Grava o SPID na tabela de log
     -----------------------------------------
     update tab_log_processo
        set tlp_tab_dat_processo = sysdate,
            tlp_tab_spid         = vSPID
      where current of c1;
     commit;

  ---
  end loop;

  --------------------------------------------
  --- Se a rotina esta' em TAB_LOG_PROCESSO,
  --- entao vSPID recebeu valor. E neste caso,
  --- o trace deve ser ligado.
  --------------------------------------------
  if vSPID is not null
    then dbms_session.set_sql_trace(TRUE);
  end if;

end LIGA_TRACE;


/*------------------------------------------------------------------
||
|| PROCEDURE DESLIGA_TRACE
||
------------------------------------------------------------------*/
procedure     DESLIGA_TRACE
              ( p_Rotina in varchar2 )
---
is
begin
  dbms_session.set_sql_trace(FALSE);
  dbms_application_info.set_module(p_Rotina, null);
end DESLIGA_TRACE;


/*------------------------------------------------------------------
||
|| PROCEDURE CLOSE_TRACE_FILE
||
------------------------------------------------------------------*/
procedure     CLOSE_TRACE_FILE
---
is
begin
   vNumLinhas := 0;
   if utl_file.is_open(vTraceFileId) then
      utl_file.fclose( file => vTraceFileId);
   end if;
end CLOSE_TRACE_FILE;


/*------------------------------------------------------------------
||
|| FUNCTION GET_TRACE_LINE
||
------------------------------------------------------------------*/
function      GET_TRACE_LINE
              ( p_SPID in pls_integer )
              return VARCHAR2
---
is
  vTraceLine  varchar2(2000);
begin

   ----------------------------------------------------------------------
   -- Determina em que diretorio esta o ALERT.LOG
   ----------------------------------------------------------------------
   select value
     into vTraceDir
     from v$parameter
     where name = 'user_dump_dest';

   ----------------------------------------------------------------------
   -- Determina o sistema operacional do servidor
   ----------------------------------------------------------------------
   select count(*)
     into vWindows
     from v$version
     where banner like '%Windows%';

   ----------------------------------------------------------------------
   -- Determina o nome do trace file baseado no sistema operacional
   ----------------------------------------------------------------------
   if vWindows = 0 then
      /* O servidor nao e' Windows, nome provavelmente em minusculas */
      vTraceName := 'ora' || lpad( p_SPID, 5, '0') || '.trc';
   else
      /*
      || O servidor e' Windows. Acredite se quiser, mas tem que testar
      || nomes com maiusculas e minusculas...
      */
      if   -- Primeiro tenta nome e extensao em maiusculas
         arquivoExiste( vTraceDir, 'ORA' || lpad( p_SPID, 5, '0') || '.TRC' )
            then vTraceName := 'ORA' || lpad( p_SPID, 5, '0') || '.TRC';
      elsif -- Agora tenta nome e extensao em minusculas
         arquivoExiste( vTraceDir, 'ora' || lpad( p_SPID, 5, '0') || '.trc' )
            then vTraceName := 'ora' || lpad( p_SPID, 5, '0') || '.trc';
      elsif -- Tenta nome em maiusculas e extensao em minusculas
         arquivoExiste( vTraceDir, 'ORA' || lpad( p_SPID, 5, '0') || '.trc' )
            then vTraceName := 'ORA' || lpad( p_SPID, 5, '0') || '.trc';
      else  -- Sobrou apenas nome em minusculas e extensao em maiusculas
         vTraceName := 'ora' || lpad( p_SPID, 5, '0') || '.TRC';
      end IF;
   end if;

   ----------------------------------------------------------------------
   -- Abre o trace file
   ----------------------------------------------------------------------
   if vNumLinhas = 0
      and not utl_file.is_open(vTraceFileId)
   then
      begin
         dbms_output.put_line('Abrindo ' || vTraceDir || '\' || vTraceName);
         vTraceFileId := utl_file.fopen
                           ( location => vTraceDir
                           , filename => vTraceName
                           , open_mode => 'r' );
         utl_file.fclose ( vTraceFileId );
      end traceOpen;
   end if;
  
   -----------------------------------------
   -- Se ja foi lido ate o fim, retorna EOF
   -----------------------------------------
   if vNumLinhas > 0 and
      not utl_file.is_open(vTraceFileId)
   then
      return('EOF');
   end if;

   -----------------------------------------
   --- Pega a proxima linha do arquivo
   -----------------------------------------
   if utl_file.is_open(vTraceFileId)
   then
      begin
         utl_file.get_line(vtraceFileId, vTraceLine);
         vNumLinhas := vNumLinhas + 1;
      exception
         -----------------------------
         when NO_DATA_FOUND then
            vEOF := TRUE;
         -----------------------------
         when UTL_FILE.INVALID_OPERATION then
            vNumLinhas := 0;
            utl_file.fclose_all;
            raise_application_error
              ( -20003, 'arquivo nao aberto para leitura');
         -----------------------------
         when UTL_FILE.READ_ERROR then
            vNumLinhas := 0;
            utl_file.fclose_all;
            raise_application_error
              ( -20004, 'ocorreu um erro no SO durante a leitura');
      end;
   end if;

   -----------------------------------------
   --- Fecha o arquivo de trace
   -----------------------------------------
   if vEOF then
      vEOF := FALSE;
      utl_file.fclose( file => vTraceFileId );
   end if;

   -----------------------------------------
   --- Retorna o texto dessa linha do arquivo de trace
   -----------------------------------------
   return vTraceLine;

exception
   -----------------------------
   when VALUE_ERROR then
      vNumLinhas := 0;
      utl_file.fclose_all;
      raise_application_error
        ( -20002, 'Linha muito longa para armazenar na variavel');
   -----------------------------
   when NO_DATA_FOUND then
     vNumLinhas := 0;
     utl_file.fclose(vTraceFileId);
     return null;
   -----------------------------
   when UTL_FILE.INVALID_OPERATION then
      vNumLinhas := 0;
      utl_file.fclose_all;
      if vTraceDir like '%$%'
      or vTraceDir like '%?%'
      or vTraceDir like '%\%%' escape '\'
      then
         return
           ( 'Nao pode abrir o Trace File. ' ||
             'Verifique o parametro de inicializacao USER_DUMP_DEST ' ||
              vTraceDir || '.'
           );
      else
         return
         ( 'Encontrado erro UTL_FILE.INVALID_OPERATION.' );
      end if;
   -----------------------------
   when UTL_FILE.INTERNAL_ERROR then
      return 
        ( 'Encontrado erro UTL_FILE.INTERNAL_ERROR. ' ||
          'Erro de origem indeterminada.'
        );
   -----------------------------
   when UTL_FILE.INVALID_FILEHANDLE then
      return 
        ( 'Encontrado erro UTL_FILE.INVALID_FILEHANDLE. ' ||
          'Verifique variavel vTraceFileId.'
        );
   -----------------------------
   when UTL_FILE.INVALID_PATH then
      vNumLinhas := 0;
      utl_file.fclose_all;
      return 
        ( 'Encontrado erro UTL_FILE.INVALID_PATH. ' ||
          'Verifique o parametro de inicializacao UTL_FILE_DIR ' ||
           vTraceDir || ' == ' || vTraceName || '.'
        );
   -----------------------------
   when UTL_FILE.INVALID_MODE then
      return 
        ( 'Encontrado erro UTL_FILE.INVALID_MODE. ' ||
          'Verifique permissao de acesso no arquivo.'
        );
   -----------------------------
   when UTL_FILE.READ_ERROR then
      return 
        ( 'Encontrado erro UTL_FILE.READ_ERROR. ' ||
          'Verifique o acesso ao arquivo no servidor.'
        );
/* -- Errors da UTL_FILE do Oracle 9i e superiores
||      -----------------------------
||      when UTL_FILE.ACCESS_DENIED then
||         return 
||           ( 'Encontrado erro UTL_FILE.ACCESS_DENIED. ' ||
||             'Verifique permissoes de acesso ao arquivo ' ||
||              vTraceDir || ' == ' || vTraceName || '.'
||           );
||      -----------------------------
||      when UTL_FILE.FILE_OPEN then
||         return 
||           ( 'Encontrado erro UTL_FILE.FILE_OPEN. ' ||
||             'Verifique se o arquivo ja esta aberto.'
||           );
||      -----------------------------
||      when UTL_FILE.INVALID_FILENAME then
||         return 
||           ( 'Encontrado erro UTL_FILE.INVALID_FILENAME. ' ||
||             'Verifique se existe o arquivo ' ||
||              vTraceDir || ' == ' || vTraceName || '.'
||           );
*/   
      -----------------------------
      when OTHERS then
         utl_file.fclose_all;
--
end GET_TRACE_LINE;
--------------------------------------------

end TRACE_PACK;
/
set termout on

show errors

-------------------------------------------------------------------*
--- Cria o sinonimo publico e concede os grants
-------------------------------------------------------------------*
GRANT EXECUTE ON PROD_DBA.TRACE_PACK TO AUD_SIS
/
GRANT EXECUTE ON PROD_DBA.TRACE_PACK TO OPE_PRD
/

CREATE PUBLIC SYNONYM TRACE_PACK FOR PROD_DBA.TRACE_PACK
/

set echo off time off timing off
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  trace_sess.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
execute dbms_system.set_sql_trace_in_session(12O2,27O68,true);

SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

-- Available from SQL*Plus since 8i (commandline utility prior to this.
SQL> CONN sys/password AS SYSDBA;  -- User must have SYSDBA.
SQL> ORADEBUG SETMYPID;            -- Debug current session.
SQL> ORADEBUG SETOSPID 1234;       -- Debug session with the specified OS process.
SQL> ORADEBUG SETORAPID 123456;    -- Debug session with the specified Oracle process ID.

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME;      -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;
 
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);The DBMS_SUPPORT package is not present by default, but can be loaded as the SYS user by executing the @$ORACLE_HOME/rdbms/admin/dbmssupp.sql script.

For methods that require tracing levels the following are valid values:

0 - No trace. Like switching sql_trace off. 
2 - The equivalent of regular sql_trace. 
4 - The same as 2, but with the addition of bind variable values. 
8 - The same as 2, but with the addition of wait events. 
12 - The same as 2, but with both bind variable values and wait events. 
The same combinations are possible for those methods with boolean parameters for waits and binds.
With the advent of Oracle 10g the SQL tracing options have been centralized and extended using the DBMS_MONITOR package. The examples below show just a few possible variations for enabling and disabling SQL trace in Oracle 10g.

-- Oracle 10g
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -
> waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');
----------------------------------
-- FIM DO SCRIPT  -- 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em ORACLE 11gR2, PLSQL SCRIPTS, PLSQL TUNING e marcado , , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s