----------------------------------
-- 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
