Database Logoff Trigger as a Data Collector(使用 LogOff触发器收集OWI信息)

1、创建记录表

create table system.session_event_history
initrans 4
as select b.sid, b.serial#, b.username, b.osuser, b.paddr,
b.process, b.logon_time, b.type, a.event, a.total_waits,
a.total_timeouts, a.time_waited, a.average_wait,
a.max_wait, sysdate as logoff_timestamp
from v$session_event a, v$session b
where 1 = 2;

create table system.sesstat_history initrans 4
as
select c.username, c.osuser, a.sid,
c.serial#, c.paddr, c.process,
c.logon_time, a.statistic#, b.name,
a.value, sysdate as logoff_timestamp
from v$sesstat a, v$statname b, v$session c
where 1 = 2;

2、创建trigger

create or replace trigger sys.logoff_trig
before logoff on database
declare
logoff_sid pls_integer;
logoff_time date := sysdate;
begin
select sid into logoff_sid from v$mystat where rownum < 2;
insert into system.session_event_history
(sid,
serial#,
username,
osuser,
paddr,
process,
logon_time,
type,
event,
total_waits,
total_timeouts,
time_waited,
average_wait,
max_wait,
logoff_timestamp)
select a.sid,
b.serial#,
b.username,
b.osuser,
b.paddr,
b.process,
b.logon_time,
b.type,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
logoff_time
from sys.v$session_event a, sys.v$session b
where a.sid = b.sid
and b.username = login_user
and b.sid = logoff_sid;

insert into system.sesstat_history
(username,
osuser,
sid,
serial#,
paddr,
process,
logon_time,
statistic#,
name,
value,
logoff_timestamp)
select c.username,
c.osuser,
a.sid,
c.serial#,
c.paddr,
c.process,
c.logon_time,
a.statistic#,
b.name,
a.value,
logoff_time
from v$sesstat a, v$statname b, v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and b.name in
('CPU used when call started', 'CPU used by this session',
'recursive cpu usage', 'parse time cpu')
and c.sid = logoff_sid
and c.username = login_user;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6456/viewspace-112250/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6456/viewspace-112250/