Estava precisando realizar um trace por usuário na versão 10gr2, e este usuário se conecta através de várias aplicações e inclusive programas para rodar queries (SQL*PLUS/TOAD/Plsql Developer/Navigator e etc) daí resolvi dar uma olhada na dbms_monitor que até já tinha postado http://mufalani.com.br/site/fazendo-traces-para-investigar-problemas/ a muito tempo atrás como usar algumas procedures deste package.
Na documentação oficial do 10gr2 (versão 10.1 foi onde a Oracle introduziu essa package) podemos achar mais informações
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#sthref4019]:
A maneira mais fácil de realizar trace de um usuário foi utilizando a procedure DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE que nos permite informar o valor para a coluna que quando igual ao da coluna CLIENT_IDENTIFIER da V$SESSION nos produz no diretório UDUMP os traces que eu precisava. Só esbarrei em um problema, como alguns blogs que consultei na net dizem que o client_identier é o username, errado, a coluna client_identifier assim como a coluna client_info são preenchidas através de packages DBMS_SESSION e DBMS_APPLICATION_INFO respectivamente e os valores na visão vem nulos por padrão.
Abaixo segue um exemplo de uma trigger de logon que serve para preencher a coluna client_identifier da v$session com o mesmo valor igual ao nome do usuário que estou me conectando ao banco. Isso é bom para não ter que solicitar nenhuma mudança em aplicações existentes.
CREATE OR REPLACE TRIGGER TESTE.SET_TRACE AFTER LOGON ON DATABASE
DECLARE
USUARIO VARCHAR2(30);
BEGIN
USUARIO := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);
IF USUARIO IN (‘TESTE’) THEN — só faz isso para o usuário teste
DBMS_SESSION.SET_IDENTIFIER(‘TESTE’);
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
Lembrando que podemos colocar qualquer valore string nesse campo.
No nosso caso, eu coloquei TESTE para ficar mais fácil o entendimento, espero que não confundam, no select abaixo podemos conferir todo mundo que está conectado com o usuário TESTE e que a trigger fez sua mágica.
SELECT *
FROM V$SESSION
WHERE CLIENT_IDENTIFIER=’TESTE’;
Feito isso, podemos então ligar o trace dos usuários que se conectaram no banco com a conta TESTE. Para tal, utilizamos a dbms_monitor conforme o exemplo abaixo:
BEGIN
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(‘TESTE’, TRUE, FALSE);
END;
/
Verifique que está habilitado consultando a visão abaixo:
SELECT *
FROM DBA_ENABLED_TRACES;
Depois de coletar traces por um período que achar razoável, pode desabilitar a coleta de traces, e verificar sempre se ele está desligado. Uma vez quase enchi o disco da Oracle home de um servidor que administrava.
Desabilitando o trace:
BEGIN
DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(‘TESTE’);
END;
/
Depois de desabilitar o trace, use o trcsess para juntar os traces em um único arquivo
trcsess [output=
output=
Depois disso, use o tkprof para formatar o trace de forma que melhor queira:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first ‘integer’ SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
Abraços,
@mufalani