Menu de navegação

[airoospParticipante]

Boa tarde,

Preciso criar um processo para capturar o que uma sessão fez no banco, e consequentemente quais as tabelas que a sessão utilizou.

exec sys.dbms_system.set_sql_trace_in_session(117,63246,true);

Através do comando acima consegui gerando um arquivo de trace, e depois através do tkprof ver de forma mais organizada as informações do arquivo.

É possível fazer este processo através de trigger on database?

Aqui não posso usar nenhum recurso que possa ser cobrado ou melhor que precisa de licença de uso.

Se alguém tiver alguma diga, agradeço.

Obrigado.

Airton

[José Laurindo ChiappaModerador]

Bom, imagino que o que vc quer é ativar o trace quando a sessão loga no banco, sim ?? Sendo isso Sim, a trigger de LOGON existe para automatizar tarefas que vc quer disparar quando uma sessão loga…. O exemplo mínimo seria lgo do tipo :

CREATE OR REPLACE TRIGGER SYS.TGR_ENABLE_TRACE
after logon on database
BEGIN
IF USER = ‘nomedousuárioquevcquermonitorar’ THEN
EXECUTE IMMEDIATE ‘alter session set TIMED_STATISTICS=TRUE’;
EXECUTE IMMEDIATE ‘alter session set STATISTICS_LEVEL=ALL’;
EXECUTE IMMEDIATE ‘alter session set max_dump_file_size=UNLIMITED’;
EXECUTE IMMEDIATE ‘ALTER SESSION SET EVENTS ”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12”’;
END IF;
END;
/

==> Não é obrigatório vc ter os WAITs completos via TIMED_STATISTICS nem deixar o tamanho do dump file unlimited, nem Ativar nível completo de estatísticas de uso (até porque o objetivo é só ter os textos dos SQLs ao que entendi) mas taí o exemplo, pra ser alterado cfrme vc quiser/precisar…

FIQUE CIENTE porém que :

a) estou supondo aqui que vc **** NÃO **** usa nenhum tipo de POOL DE CONEXÃO, e portanto a cada vez que alguém vai conectar no banco um NOVO LOGIN é feito, E as sessões não ficam ‘eternamente’ penduradas…
OBVIAMENTE se houver pool de conexão, shared sessions/MTS ou equivalentes o software de POOLING ao startar já cria n sessões e as vai distribuindo entre os usuários , aí É CLARO que não vai haver logon no banco (as sessões já logaram antes e estão sendo compartilhadas – esse é o OBEJTIVO de um connection pool!!) e PORTANTO essa trigger NÂO FUNCIONARÁ!!! Num caso assim vc VAI ter que apelar pro end-to-end tracing via DBMS_MONITOR, https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/06/30/tracking-down-a-3-minute-weird-database-performance-issue-with-end-to-end-application-monitoring-tracking-approach e https://dioncho.wordpress.com/tag/end-to-end-tracing/ são exemplos…

b) isso *** VAI *** ter algum custo em termos de PERFORMANCE e de ESPAÇO EM DISCO : o nível exato de intereferência na performance e no gasto de espaço vai DEPENDER de qtdade de sessões e ‘comprimento’ das transações/tempo de duração das sessões entre n outros fatores, mas que VAI TER INTERFERÊNCIA é batata…
Inclusive, o parâmetro LEVEL do evento 10046 é crítico aqui : com ele vc pde indicar se quer wait events e valores de bind variables ou não : se vc quiser só o texto dos SQLs vc pode tentar um LEVEL 2…. Veja http://www.dba-oracle.com/t_10045_trace_events_level_2_4_8_12.htm como ref pros seus testes

c) DE FORMA ALGUMA trace é o melhor e a única maneira pra vc obter apenas a lista de operações que foram feitas no banco : o RDBMS Oracle fornece *** MUITÍSSIMAS OUTRAS *** opções de AUDITORIA, que é ao que entendi seu objetivo… Entre outras opções gratuitas/built-on no RDBMS (já que vc diz que não tem verba pra nada), *** AVALIE *** o comando AUDIT, o Fine-Grainded Audit (via DBMS_FGA) e (*** SE *** vc mantém os logs arquivados por tempo suficiente) a auditoria via mineração de redo logs com LOGMINER : http://www.nocoug.org/download/2008-05/LogMiner4.pdf é um artigo sobre LOGMINER e https://oracle-base.com/articles/10g/auditing-10gr2 mostra um pouco do FGA e do AUDIT…
As *** VANTAGENS *** desses caras é que em princípio devem intereferir MENOS na performance (já que são opções NATIVAS, compiladas em C dentro do RDBMS) e exigem MUITO MUITO MENOS programação por sua parte, e a DESVANTAGEM principal é que são opções FECHADAS, ie, registram e fazem só o mínimo que está Documentado : se vc quiser/precisar de logs a mais não tem como…

[]s

Chiappa

[Falcao1000Participante]

Boa Tarde,

Eu resolvi aqui com a criação da trigger abaixo. É um controle para usuários com acesso direto ao banco via ferramentas, filtrando user que não devam ser auditados . Só cuidado com o espaço utilizado, gera muito arquivo de trace dependendo da utilização da base. Aqui eu compacto, envio para backup em fita e removo os arquivos com mais de 7 dias via shell script.

CREATE OR REPLACE TRIGGER SYS.set_trace_tools
AFTER LOGON on database

declare
v_user varchar2(200);
v_mod varchar2(200);
v_prog varchar2(200);
v_mac varchar2(200);
v_osuser varchar2(200);
v_sid varchar2(200);
v_terminal varchar2(200);
v_ltime date;
begin
select lower(username),upper(program), upper(module), upper(machine),
replace(osuser, ‘.’,’_’) , logon_time into v_user,v_prog, v_mod, v_mac, v_osuser, v_ltime
from v$session
where AUDSID = USERENV(‘sessionid’) and rownum = 1;

IF v_prog LIKE ‘%TOAD%’ OR –TOAD
v_prog LIKE ‘%T.O.A.D%’ OR — TOAD2
v_prog LIKE ‘%SQLNAV%’ OR — SQL Navigator
v_prog LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
v_prog LIKE ‘%BUSOBJ%’ OR — Business Objects
v_prog LIKE ‘%EXCEL%’ OR — MS Excel plug in
v_prog LIKE ‘%SQLPLUS%’ OR — SQLPLUS
v_prog LIKE ‘%SQLTOOLS%’ OR — SQLTOOLS
v_prog LIKE ‘%DEVELOPER%’ OR — SQL DEVELOPER
v_prog LIKE ‘%MSACCESS%’ OR — Access
v_mod LIKE ‘%TOAD%’ OR –TOAD
v_mod LIKE ‘%T.O.A.D%’ OR –TOAD2
v_mod LIKE ‘%SQLNAV%’ OR — SQL Navigator
v_mod LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
v_mod LIKE ‘%BUSOBJ%’ OR — Business Objects
v_mod LIKE ‘%EXCEL%’ OR — MS – Excel plug in
v_mod LIKE ‘%SQLPLUS%’ OR –SQLPLUS OR
v_mod LIKE ‘%SQLTOOLS%’ OR –SQLTOOLS OR
v_mod LIKE ‘%DEVELOPER%’ OR –SQL DEVELOPER
v_mod LIKE ‘%MSACCESS%’ — Access

THEN

if upper(rtrim(ltrim(v_user))) not in (‘MONITORACAO’,’ATUALIZA_BASE’) then

execute immediate ‘alter session set tracefile_identifier = ‘||substr(v_osuser,1,18)||’_user_’||substr(v_user,1,18);
execute immediate ‘alter session set max_dump_file_size=”4096M”’;
execute immediate ‘alter session set events ”10046 trace name context forever, level 8”’;

end if;

END IF;
END;

[Autor]

Posts