Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

logon and logoff trigger modification

Rafi (Oracle DBA)Dec 15 2010 — edited Dec 15 2010
Hi Friends,
I have logon and logoff triggers enable on my 11g rel2 Database,Each time user is loggin on and logging off entry is recorded in my table stats$user_log which has the structure as given below:
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)
)
;

logon trigger:

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;
/
The logoff trigger:

create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/

Entry:
SQL> select user_id||' '||last_action from stats$user_log;
USER_ID||''||LAST_ACTION
KTU1KOR
KTU1KOR
KTU1KOR
KTU1KOR
I want only one entry for each user_id whenever they log on or log off how can I modify the table
structure or triggers to obtain this.
I want the entry like this:

USER_ID||''||LAST_ACTION
TEST_PROF
TEST_PROFILE
rrr
SYS
KTU1KOR
Best regards,

Rafi.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 12 2011
Added on Dec 15 2010
4 comments
702 views