Browse By

Tempo conectado – Usando DBA_AUDIT_TRAIL

 

Vou postar  um script que customizei utilizando a view DBA_AUDIT_TRAIL para analisar o tempo que uma determinada sessão ficou conectada no banco de dados. Como utilizo muito essa informação fica mais fácil de procurar aqui quando eu estiver em algum outro lugar sem acesso ao dropbox, meu note particular e etc.

As linhas ao final podem ser descomentadas caso precise de uma busca mais específica. Antes que alguém pergunte, adicionei 15 minutos no select interno devido a função lag para que ela já me traga nos primeiros registros “pais” com valores não nulos. Se seu banco possui poucas conexões pode aumentar para um dia se precisar. São só “flores”..rs

No Oracle 12c/11g por default esse tipo de auditoria de conexões já é habilitado, confira aqui.

Para versões anteriores será necessário configurar o parâmetro audit_trail para DB e habilitar o tipo de auditoria para logons: AUDIT CREATE SESSION ou criar alguma trigger de auditoria na qual acho desnecessário para “essa tarefa” pois o próprio RDBMS pode resolver.

##################################################################
# Criador: Wellington Prado
# Created: 20/04/2014
# Descrição: Análise tempo de conexão usado DBA_AUDIT_TRAIL
# www.wellingtonprado.com
##################################################################
#
# Compatible: 
# Oracle 12c Enable default
# Oracle 11g Enable default
# Oracle 10g (Habilitar audit_trail / audit create session)
# Necessário comentar algumas colunas
# Oracle 9i (Habilitar audit_trail / audit create session)
# Necessário comentar algumas colunas
#
##################################################################
SELECT OS_PROCESS,
 os_username,
 username,
 userhost,
 CASE
 WHEN action_name = 'LOGON'
 THEN rpad('Logon ',13)
 ||TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi:ss') 
 WHEN action_name = 'LOGOFF'
 THEN rpad('Logout ',13)
 ||TO_CHAR(logoff_time,'dd/mm/yyyy hh24:mi:ss')
 WHEN action_name = 'LOGOFF BY CLEANUP'
 THEN rpad('Force Logout ',13)
 ||TO_CHAR(logoff_time,'dd/mm/yyyy hh24:mi:ss')
 END AS CON_STATUS,
 CASE
 WHEN Tempo_logado IS NULL
 THEN '---'
 ELSE Tempo_logado
 END AS TEMPO_CONECTADO
FROM
 (SELECT SESSIONID
 ,OS_PROCESS
 ,os_username
 ,username
 ,userhost
 ,TIMESTAMP
-- ,action
 ,action_name
 ,priv_used
 ,logoff_time
 ,TO_CHAR(to_date(((extract(hour FROM CAST(logoff_time AS TIMESTAMP)) * 60 * 60 + extract(minute FROM CAST(logoff_time AS TIMESTAMP)) * 60 + extract(second FROM CAST(logoff_time AS TIMESTAMP))) - (extract(hour FROM CAST((lag(TIMESTAMP) over (partition BY SESSIONID order by SESSIONID,TIMESTAMP)) AS TIMESTAMP)) * 60 * 60 + extract(minute FROM CAST((lag(TIMESTAMP) over (partition BY SESSIONID order by SESSIONID,TIMESTAMP)) AS TIMESTAMP)) * 60 + extract(second FROM CAST((lag(TIMESTAMP) over (partition BY SESSIONID order by SESSIONID,TIMESTAMP)) AS TIMESTAMP)))),'sssss'),'hh24:mi:ss') Tempo_logado
-- , SESSION_CPU
-- , LOGOFF_LREAD
-- , LOGOFF_PREAD
-- , LOGOFF_LWRITE
-- , LOGOFF_DLOCK
-- , extended_timestamp
-- , comment_text
 FROM dba_audit_trail
 WHERE action_name IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
 AND TIMESTAMP > (sysdate-&dias) +15/(24*60)
 ORDER BY SESSIONID,
 TIMESTAMP
 )
WHERE TIMESTAMP > SYSDATE-&dias
 --AND USERNAME LIKE '%&USERNAME%'
 --AND OS_USERNAME LIKE '%&OS_USER&%'
 --AND USERHOST LIKE '%&HOST%'
 --AND TIMESTAMP BETWENN TO_DATE('01/01/2014 10:00:00', 'DD/MM/YYYY HH24:MI:SS') AND TO_DATE('10/01/2014 10:00:00', 'DD/MM/YYYY HH24:MI:SS')

Forte abraço

 

Leave a Reply

Your email address will not be published. Required fields are marked *