Re: AUD$ TIMESTAMP and LOGOFF_TIME are equal
- From: Yong Huang <yong321@xxxxxxxxx>
- To: Chandra_Pabba@xxxxxxxxxxx, ric.van.dyke@xxxxxxxxxx
- Date: Thu, 31 Dec 2009 22:48:58 -0800 (PST)
> Looks like the behavior has changed with 11g (both R1 and R2 -
> probably a bug!)
You're right. I remembered wrong. I just did a SQL trace. In
10.2.0.4, there's one insert into aud$ at session logon, and one update
at logoff as shown below (I omitted irrelevant part but kept all time
related info):
At logon:
insert into sys.aud$(...,ntimestamp#, ...)
values(...,SYS_EXTRACT_UTC(SYSTIMESTAMP),...)
At logoff :
update sys.aud$ set ..., logoff$time=cast(systimestamp as date), ... where
sessionid=:1 and entryid=1 and action#=100
But in 11g (mine is 11.2.0.1), both are inserts:
At logon:
insert into sys.aud$(...,ntimestamp#, ...)
values(...,SYS_EXTRACT_UTC(SYSTIMESTAMP),...)
At logoff:
insert into sys.aud$(...,ntimestamp#,..., logoff$time,...)
values(...,SYS_EXTRACT_UTC(SYSTIMESTAMP),
...,cast(SYS_EXTRACT_UTC(systimestamp) as date),...)
Due to this change, 11g Oracle no longer needs the index i_aud1 on
(sessionid, ses$tid) to speed up the update at session logoff. The
overall space usage should be less than doubled for "audit session".
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
