Re: AUD$ TIMESTAMP and LOGOFF_TIME are equal

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       

--
http://www.freelists.org/webpage/oracle-l