rem $dba/audses.sql
rem
rem Creates an audit report of the current month's usage by username.
rem
set echo off
set feedback off
set verify off
set linesize 80
set pagesize 9999
set term off
column TODAY NEW_VALUE CURRENT_DATE NOPRINT format a1 trunc;
column TIME NEW_VALUE CURRENT_TIME NOPRINT format a1 trunc;
SELECT TO_CHAR(SYSDATE,'MM/DD/YY') TODAY, TO_CHAR(SYSDATE,'HH:MI AM') TIME
	FROM DUAL;
column os_username format a12 heading "Operating|system|name"
column username format a15 heading "Oracle|user|name"
column pass_fail format a11 heading "Audited|Action"
column timein format a17 heading "Time|In"
column timeout format a17 heading "Time|Out"
set pagesize 9999
spool audses.lis
select	os_username,
	username,
	decode(returncode,'0','Connected','1005','Failed Null',
		'1017','Failed',returncode) pass_fail,
	to_char(timestamp, 'MM/DD/YY HH24:MI:SS') timein,
	to_char(logoff_time,'MM/DD/YY HH24:MI:SS') timeout
from dba_audit_session
where timestamp between
	to_date('01'||substr((to_char(sysdate)),3,7)) and sysdate;
spool off
set term on
prompt Created audses.lis...
exit