Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Monday, April 30, 2012

Oracle AUD$ TIMESTAMP# from 9i to 10gR2

Print Friendly and PDF
You select TIMESTAMP# from AUD$ in an Oracle 10gR2 or 11g database, but you seem to be missing the most recent audit information.
Your DB was upgraded at some point from 9i or earlier.

SQL> select min(TIMESTAMP#) from aud$;
MIN(TIMES
---------
04-MAR-04

SQL> select max(TIMESTAMP#) from aud$;
MAX(TIMES
---------
01-JUN-10


When you select from the DBA_AUDIT_TRAIL view, you see all the records:

SQL> select max(timestamp) from dba_audit_trail;
MAX(TIMES
---------
26-APR-12 07.48.13.242725 AM


The reason is that the TIMESTAMP# column in AUD$ was made obsolete in 10.1.0.5, but it still contains the old data.
The old data was migrated into the NTIMESTAMP# column but it will be missing the additional sub-second timings and the timezone:

SQL> select min(NTIMESTAMP#) from aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
04-MAR-04 03.43.28.000000 PM


You should use the NTIMESTAMP# column in AUD$ to see the later audit records post 10g upgrade.
See MYOS note 427296.1 for more detail on this.

The DBA_AUDIT_TRAIL view uses the new NTIMESTAMP# column:

SQL> select max(timestamp) from dba_audit_trail ;
MAX(TIMES
---------
26-APR-12 07.48.13.242725 AM


SQL> select min(timestamp) from dba_audit_trail;
MIN(TIMES
---------
04-MAR-04 03.43.28.000000 PM

No comments: