Auditing Oracle Database Logons
I’m running a project at work at the moment to clean up some of our old version 8 and 9 databases. To do this I had to somehow track if anyone was logging into them and who. Luckly the team at Burleson consulting had some good information about Auditing and reporting Oracle user activity.
It consists of a table to store the information and a trigger that fires when anyone logs into the database.
create table stats$user_log ( user_id 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) );
create or replace trigger logon_audit_trigger AFTER LOGON ON DATABASE BEGIN insert into stats$user_log values( user, sys_context('USERENV','SESSIONID'), sys_context('USERENV','HOST'), null, null, null, sysdate, to_char(sysdate, 'hh24:mi:ss'), null, null, null ); END; /
To view who has logged into the database you simply need to query the stats$user table like this.
select distinct user_id, logon_day from sys.stats$user_log;
I found it very helpful and was able to identify 4 databases that weren’t being used.
NOTE : I would be careful how long you leave the trigger in place. If the database is allot busier than you think it is, then the table could grow is size quite rapidlly.