Scripts
Query dba_audit_session
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Monday, 27 July 2015 14:51
-
Last Updated on Monday, 27 July 2015 21:12
-
Published on Monday, 27 July 2015 14:51
-
Hits: 9084
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.