RE: AUD$ TIMESTAMP and LOGOFF_TIME are equal

  • From: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 Jan 2010 14:54:31 -0500

FYI, this behavior can be replicated in 10.2.0.4. if you apply patch 8648568. 

Vlado Barun, M.Sc., OCE SQL, OCA, MCP
Sr. Database Architect/Manager, Database Engineering and Operations
Jewelry Television


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Yong Huang
Sent: Friday, January 01, 2010 1:49 AM
To: Chandra_Pabba@xxxxxxxxxxx; ric.van.dyke@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: AUD$ TIMESTAMP and LOGOFF_TIME are equal

> 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


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


Other related posts: