17 | 10 | 2017
Latest Articles
Popular Articles

Scripts

Query dba_audit_session

User Rating:  / 0
PoorBest 

Query DBA_AUDIT_SESSION.

 

You have set dba_audit_trail=DB and you have decided to audit logout and logoff of database sessions

( this is the default db configuration from 11G R1 onwards )

This is a query you can use to retrieve the number of entries for a given user per month and per year

 

WITH row_source AS
( select trunc(timestamp,'YYYY') "YEAR",
trunc(timestamp,'MM') "MONTH",
count(*) "ENTRIES_PER_MONTH"
from dba_audit_session
where username='&WHICH_USER'
group by trunc(timestamp,'YYYY'),trunc(timestamp,'MM') )
select YEAR,
MONTH,
ENTRIES_PER_MONTH,
sum(ENTRIES_PER_MONTH) over (PARTITION by YEAR order by MONTH) "TOTAL_ENTRIES_SO_FAR"
from row_source
order by 1,2;

 

sample output

 

start audit_session_info.sql
Enter value for which_user: COCONUT
old 6: where username='&WHICH_USER'
new 6: where username='COCONUT'

 

YEAR             MONTH            ENTRIES_PER_MONTH TOTAL_ENTRIES_SO_FAR
---------------- ---------------- ----------------- --------------------
01/01/2014 00:00 01/05/2014 00:00                 7                    7
01/01/2014 00:00 01/06/2014 00:00                 4                   11
01/01/2014 00:00 01/07/2014 00:00                 8                   19
01/01/2014 00:00 01/08/2014 00:00                 4                   23
01/01/2014 00:00 01/09/2014 00:00                 6                   29
01/01/2014 00:00 01/10/2014 00:00                 8                   37
01/01/2014 00:00 01/11/2014 00:00                 4                   41
01/01/2014 00:00 01/12/2014 00:00                 6                   47
01/01/2015 00:00 01/01/2015 00:00                 8                    8 --- RESET
01/01/2015 00:00 01/02/2015 00:00                 4                   12
01/01/2015 00:00 01/03/2015 00:00                 6                   18
01/01/2015 00:00 01/04/2015 00:00                 8                   26
01/01/2015 00:00 01/05/2015 00:00                 4                   30
01/01/2015 00:00 01/06/2015 00:00                 8                   38
01/01/2015 00:00 01/07/2015 00:00                11                   49

15 rows selected.