Skip to content
Home » Oracle » How to Check LAST_ARCHIVE_TIME

How to Check LAST_ARCHIVE_TIME

Before you purge some unified audit trails, you may like to know what the current value of LAST_ARCHIVE_TIME has been set by SET_LAST_ARCHIVE_TIMESTAMP.

Theoretically, we can get the information by executing GET_LAST_ARCHIVE_TIMESTAMP function, but unluckily, the function is only for read only databases. Let's see what Oracle says about it. (28.7.12 GET_LAST_ARCHIVE_TIMESTAMP Function)

In a database that is opened for READ WRITE, since there will no timestamp stored in SGA memory, this function will return NULL. But in a database that is opened for READ ONLY, if a timestamp is set by the SET_LAST_ARCHIVE_TIMESTAMP Procedure, the timestamp will be returned. Else it will return NULL.

The most official way to check current LAST_ARCHIVE_TIME is to lookup DBA_AUDIT_MGMT_LAST_ARCH_TS, a dictionary view which contains information about the last archive timestamps set for purging unified audit trail..

SQL> select LAST_ARCHIVE_TS from DBA_AUDIT_MGMT_LAST_ARCH_TS;

LAST_ARCHIVE_TS
---------------------------------------------------------------------------
11-JUL-22 10.38.11.619816 AM +00:00

Please note that, it may return "no rows selected", then it means that LAST_ARCHIVE_TIME is cleared by CLEAR_LAST_ARCHIVE_TIMESTAMP or has never been set.

Leave a Reply

Your email address will not be published. Required fields are marked *