Oracle 11.2.0.3 SE-One
Oracle Linux 5.6 x86-64
Here's an anomaly I noticed while working with the dbms_audit_mgmt package.
In the following script, note that I am setting last_archive_timestamp on all 4 audit trail types, and I am setting them in exactly the same way.
After setting it, I query DBA_AUDIT_MGMT_LAST_ARCH_TS to see the results.
What I notice and can't explain is that the time zone offsets are not the same. I would have expected them to be all GMT or all local, but it's a mix.
oracle:mysid$ cat doit.sql
set trimsp on tab off
conn / as sysdba
spo doit.lis
set head off feedback off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
--
set head on feedback on
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
END;
/
--
col last_archive_ts for a36
select audit_trail,
last_archive_ts
from DBA_AUDIT_MGMT_LAST_ARCH_TS
order by audit_trail;
spo off
exit
And here is the result. Notice that two of the audit trails show LAST_ARCHIVE_TS with a timezone offset of +00:00, and the other two are -05:00.
oracle:mysid$ cat doit.lis
PL/SQL procedure successfully completed.
AUDIT_TRAIL LAST_ARCHIVE_TS
-------------------- ------------------------------------
FGA AUDIT TRAIL 28-MAR-14 12.00.00.000000 AM +00:00
OS AUDIT TRAIL 28-MAR-14 12.00.00.000000 AM -05:00
STANDARD AUDIT TRAIL 28-MAR-14 12.00.00.000000 AM +00:00
XML AUDIT TRAIL 28-MAR-14 12.00.00.000000 AM -05:00
4 rows selected.