---------------------------------- -- 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 --
Anúncios