tracefile.sql

----------------------------------
-- SCRIPT  tracefile.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem ---
rem traceFile.sql
rem
rem Comments:   Show errors in alert.log from the last n hours.
rem             It works for both UNIX and Windows databases.
rem             For Oracle 7.3 and higher.
rem             Check the format of the alert log file before.
rem
rem Required:   * utl_file_dir must contain background_dump_dest
rem             * background_dump_dest parameter must be a complete directory
rem               name, in the form '?/rdbms/log' or '%RDBMS73%' etc
rem
rem Author:     Laurent Baylac
rem             http://www.dba-village.com/village/dvp_papers.PaperDetails?PaperIdA=2383
rem
rem 14/05/2012 ALEXANDRE PIRES
rem ---

define p_SPID=&1
define pPula=&2
variable g_NumLinhas number
variable g_ErrMsg varchar2(255)

set tab off
set termout off feedback off verify off pagesize 0

column pTraceFileName new_value pTraceFileName
column pPula new_value pPula
select decode( '&2', null, '0', '&2' ) as pPula
     , 'ora'
    || lpad('&p_SPID.', 5, '0')
    || '_'
    || decode( '&2', null, '0000000', lpad('&2', 7, '0' ) )
    || '.trc' as pTraceFileName
  from dual;

spool &pTraceFileName

DECLARE
   vSID            v$database.name%type;   /* nome do database */
   vWindows        pls_integer;            /* identifica se servidor usa Windows ou nao */
   vTraceDir       v$parameter.value%type; /* diretorio do TRACE FILE */
   vTraceName      varchar2( 19);          /* nome real do arquivo TRACE FILE */
   vTraceFileId    utl_file.file_type;     /* ID de arquivo para o TRACE FILE */
   vEOF            boolean := false;       /* variavel para identificar fim do arquivo */
   vTexto          varchar2( 2000);        /* Texto de uma linha do TRACE FILE */
   vNumLinhas      pls_integer := 0;       /* Numero de linhas ja lidas */
   vPulaLinhas     pls_integer := &pPula;  /* Numero de linhas para saltar do comeco do arquivo */
   ---
   --- Tratamento erro da DBMS_OUTPUT: "ORU-10027: buffer overflow, limit of 1000000 bytes"
   eORU_10027      exception;
   pragma          EXCEPTION_INIT( eORU_10027, -10027 );
   /*
   || Funcao para testar se o arquivo existe
   */
   function arquivoExiste
           ( pDiretorio      varchar2
           , pArquivo        varchar2
           )
   return boolean as
      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
       return FALSE;
   end arquivoExiste;

BEGIN

   ----------------------------------------------------------------------
   -- Determina o nome do database
   ----------------------------------------------------------------------
   select name
     into vSID
     from v$database;

   ----------------------------------------------------------------------
   -- Determina em que diretorio esta o TRACE FILE
   ----------------------------------------------------------------------
   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. */
      if arquivoExiste( vTraceDir, '&pTraceFileName' )
      then
         vTraceName := 'ora' || lpad( &p_SPID, 5, '0') || '.trc';
      elsif arquivoExiste( vTraceDir, 'ora' || lpad( &p_SPID, 4, '0') || '.trc' )
      then
         vTraceName := 'ora' || lpad( &p_SPID, 4, '0') || '.trc';
      else
         vTraceName := 'ora' || to_char( &p_SPID, 'fm99999') || '.trc';
      end IF;
   else
      /* o servidor e' Windows */
      vTraceName := 'ORA' || lpad( &p_SPID, 5, '0') || '.TRC';
   end if;

   ----------------------------------------------------------------------
   -- Abre o arquivo TRACE FILE
   ----------------------------------------------------------------------
   begin
      vTraceFileId := utl_file.fopen
                      ( location => vTraceDir
                      , filename => vTraceName
                      , open_mode => 'r'
                      , max_linesize => 1024
                      );
   exception
      when utl_file.invalid_mode then
         raise_application_error
         ( -20001, 'string de OPEN_MODE invalida');

   end;

   ----------------------------------------------------------------------
   -- Processa todas as linha do TRACE FILE
   ----------------------------------------------------------------------
   while not vEOF
   loop
      -------------------------------------------------------------------
      --- Tenta ler uma linha do arquivo
      -------------------------------------------------------------------
      begin
         utl_file.get_line( vTraceFileId, vTexto );
         vNumLinhas := vNumLinhas + 1;
      exception
         -----------------------------
         when NO_DATA_FOUND then
            vEOF := TRUE;
         -----------------------------
         when VALUE_ERROR then
            utl_file.fclose_all;
            raise_application_error
            ( -20002, 'Linha muito longa para armazenar na variavel');
         -----------------------------
         when UTL_FILE.INVALID_OPERATION then
            utl_file.fclose_all;
            raise_application_error
            ( -20003, 'arquivo nao aberto para leitura');
         -----------------------------
         when UTL_FILE.READ_ERROR then
            vTexto := 'Linha ' || vNumLinhas
                   || ': ocorreu um erro no SO durante a leitura';
      end;

      -------------------------------------------------------------------
      --- Faz o output da linha
      -------------------------------------------------------------------
      if vNumLinhas > vPulaLinhas
         then dbms_output.put_line( substr(vTexto, 1, 254) );
      end if;

   end loop;

   ----------------------------------------------------------------------
   -- Fecha o arquivo TRACE FILE
   ----------------------------------------------------------------------
   utl_file.fclose( file => vTraceFileId);

exception
   -----------------------------
   when UTL_FILE.INVALID_PATH then
      dbms_output.put_line
      ( 'Encontrado erro UTL_FILE.INVALID_PATH. ' ||
        'Verifique o parametro de inicializacao UTL_FILE_DIR.'
      );
   -----------------------------
   when UTL_FILE.INVALID_OPERATION then
      if vTraceDir like '%$%'
      or vTraceDir like '%?%'
      or vTraceDir like '%\%%' escape '\'
      then
         dbms_output.put_line
         ( 'Nao pode abrir o TRACE FILE. ' ||
           'Verifique o parametro de inicializacao BACKGROUND_DUMP_DEST' ||
            vTraceDir || '.'
         );
      else
         dbms_output.put_line
         ( 'Encontrado erro UTL_FILE.INVALID_OPERATION.');
      end if;
   -----------------------------
   when OTHERS then
      :g_NumLinhas := vNumLinhas;
      :g_ErrMsg := SQLERRM;
END;
/
spool off
set termout on feedback 6 pagesize 10000

column "Mensagem de erro" format a70
select '&pTraceFileName' as "Trace File"
    , :g_NumLinhas as "NumLinhas"
    , :g_ErrMsg as "Mensagem de erro"
from dual
/
----------------------------------
-- 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