Experiment for auditing USERS login information for a period of time for existing users in Database

Hi,
In our development environment we planned to release the disk space and drop those users who have not login to the database since last 2 months,For this purpose
we decided to create an audit table and enable a trigger to track logon details and the OS users who will login to Database for a period of two months,once after knowing these the users who have not logon will will be dropped and the disk space will be recovered.Below are the steps we followed:

Step 1: Crating user audit table
-------
connect sys/manager;

create table
stats_user_logon
(
user_id varchar2(30),
osuser varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

Step 2: Designing a logon trigger
--------

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
declare
cnt number;
us varchar2(30);
BEGIN
select user into us from dual;
select count(*) into cnt from stats_user_logon where user_id=us;
if cnt > 0 then
UPDATE stats_user_logon SET LOGON_TIME=to_char(sysdate,'hh24:mi:ss'),LOGON_DAY=SYSDATE where user_id=us;
ELSE
insert into stats_user_logon values(
user,
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate,'hh24:mi:ss'),
null,
null,
null
);
END IF;
END;
/

Step 3:Enable triggers for log on and log off
---------

Alter trigger logon_audit_trigger enable;

Note: When you create the trigger by default it is enable,the above step is only for
reference,we can check this with the help of the below query also:

SQL> select owner||' '||trigger_name||' '||table_owner||' '||status from dba_triggers
WHERE trigger_name like '%LOGON%';

OWNER||''||TRIGGER_NAME||''||TABLE_OWNER||''||STATUS
--------------------------------------------------------------------------------
SYS LOGON_AUDIT_TRIGGER SYS ENABLED


Step 4:Tracking the users from the below query:
-------

SQL> select user_id||' '||osuser||' '||logon_day||' '||logon_time
from stats_user_logon;
2
USER_ID||''||OSUSER||''||LOGON_DAY||''||LOGON_TIME
--------------------------------------------------------------------------------
T1 oracle 17-DEC-10 18:05:20
T2 oracle 16-DEC-10 18:49:32
TEST_PROF aagppp 16-DEC-10 18:58:41
T3 ARL1MAC 17-DEC-10 18:51:43

The above experiment is used to track the present users.I have implemented one more experiment to track the future users with the help of profile that i will be posting soon.

Hope it helps.


Best regards,

Rafi.