rem Name:    aud.sql
rem Purpose: Display AUDIT SESSION history
rem Usage:   @aud <since> <%user%> <%|u|l|d|a>
rem Subject: security
rem Attrib:  sql dml dba
rem Descr:
rem Notes:
rem         % list all audit records
rem          u list audit records grouped by USERNAME (succesful only)
rem          l same as u but includes non-succesful
rem          d total by day
rem          a grand total

@setup

define dt="trunc(decode('&&1','%',sysdate-9999,decode(substr('&&1', 1, 1),'-',sysdate+to_number('&&1'),to_date('&&1'))))"
define usr="upper('&&2')"
define arg="upper('&&3')"
define grp="decode(&&arg,'L',username,'D',trunc(timestamp),'A','Totals',username)"

define hrs="((nvl(logoff_time,sysdate)-timestamp)*24)"
define mins="((nvl(logoff_time,sysdate)-timestamp)*24*60)"
define secs="((nvl(logoff_time,sysdate)-timestamp)*24*60*60)"
define logio="(nvl(logoff_lread+logoff_lwrite,0))"

column username  format a15 heading "USERNAME"
column timestamp format a14 heading "LOGON"
column logons  format 99,990 heading "LOGONS"
column logoff_time  format a14      heading "LOGOFF"
column time_elapsed format a10 heading "ELAPSED"
column elapsed_h        format 99,990.99 heading "ELAPSED_H"
column logio  format 99,999,990 heading "LOGIO"
column logio_k  format 999,990 heading "LOGIO_K"
column logio_rate format 999,990  heading "LOGIO/M"
column phy_read         format a5       heading "PHYRD"
column phy_write format a5 heading "PHYWR"

select
  username,
  to_char(timestamp, 'MONDD HH24:MI') timestamp,
  decode(logoff_time,
    NULL, 'ACTIVE',
    to_char(&&mins, '990.99')||'m'
  ) time_elapsed,
  &&logio logio,
  &&logio/(&&mins+0.01) logio_rate,
  to_char(nvl((logoff_pread/(logoff_lread+1))*100, 0), '990')||'%' phy_read,
  to_char(nvl((logoff_lwrite/(logoff_lread+1))*100, 0), '990')||'%' phy_write
from sys.dba_audit_session
where &&arg = '%'
and upper(username) like &&usr
and timestamp >= &&dt
;

select
  &&grp username,
  count(timestamp) logons,
  sum(&&hrs) elapsed_h,
  sum(&&logio/1000) logio_k,
  avg(&&logio/(&&mins+0.01)) logio_rate,
  to_char(avg(nvl((logoff_pread/(logoff_lread+1))*100, 0)), '990')||'%' phy_read,
  to_char(avg(nvl((logoff_lwrite/(logoff_lread+1))*100, 0)), '990')||'%' phy_write
from sys.dba_audit_session
where (&&arg IN ('U', 'L', 'D', 'A'))
and (&&arg = 'L' OR returncode = 0)
and upper(username) LIKE upper('&&2')
and timestamp >= &&dt
group by &&grp
order by 4 desc
;

undef dt usr arg grp hrs mins secs logio

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips