How to trace a user session on logon
Introduction:
Some times it is not easy to trace a user session, especially if user is using forms applicaiton. For forms applicaiton some times single user open 2 session in database. So one have to trace both the sessions, otherwise valueable information can miss. And offcourse it takes time to tell user, that once you logged in then wait, dont do any thing, till I tell you to do so. Once user get logged in, then you have to go to database and need to get SID and SERIAL#, and then tracing can be enabled.
Logon Triggers:
To overcome on this probelm, we can make a trigger on logon event of database, the trigger will be fired as soon user logs in to database. And it will start tracing the session automatically.
Trigger code:
Following is the trigger, which will do tracing automatically for user "TEST". Also we can make 2 tables to log the information, these tables can be checked that whether the tracing was done successfully or not. We have 2 tables here, one for the successfull tracing information and other to handle the exception.
Following are the codes for tables and trigger.
1- To log the successfull tracing information.
create table trace_start_log (status varchar2(35),trace_date date);
2- To log the exception, in case of any error.
create table trace_error_log (status varchar2(35),trace_date date);
3- Logon Trigger
CREATE OR REPLACE TRIGGER SESSION_TRACE
AFTER LOGON ON DATABASE
declare
v_user varchar2(30);
begin
select user into v_user from dual;
if v_user='TEST_USER' then
execute immediate 'alter session set sql_trace=true';
insert into trace_start_log values('trace started for TEST_USER',sysdate);
end if;
exception
when no_data_found then
insert into trace_error_log values(' no data found for TEST_USER',sysdate);
end;
/
Testing of the trigger:
We can check by logging in by TEST_USER, and we can check the table TRACE_START_LOG, also one can find the trace files in udump directory.
sql>select * from trace_start_log;
STATUS TRACE_DAT
----------------------------------- ---------
trace started for TEST_USER 28-JAN-09
