Purge the Unified Audit Trail in Oracle AI Database 26ai

Authorized administrators can remove old unified audit records in Oracle AI Database 26ai with the supported DBMS_AUDIT_MGMT package. Purging is not an ordinary table-maintenance task, however. Audit records may be security evidence, may be subject to a legal hold, and may have to remain recoverable for a documented retention period. The safe sequence is to retain the required online evidence, archive records that must be preserved elsewhere, mark the verified archive boundary, and purge only the records older than that boundary.

This lesson continues the policy lifecycle from Lesson 7. Running NOAUDIT POLICY stops future collection for the specified policy scope, and DROP AUDIT POLICY removes a policy definition. Neither command erases records already written to UNIFIED_AUDIT_TRAIL. Those records remain until an authorized audit administrator manages them through Oracle's audit-trail lifecycle procedures.

Traditional auditing is desupported in Oracle 26ai. The current workflow therefore does not delete from SYS.AUD$ or SYS.FGA_LOG$, truncate an Oracle-managed audit table, remove audit files with operating-system commands, or initialize cleanup with a legacy script. It uses unified auditing and DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED throughout.

Separate Retention, Archival, the Cutoff, and Purging

The walkthrough uses 30 days only as an instructional retention period. A production cutoff must follow the organization's records schedule, regulatory obligations, incident-response requirements, legal holds, and tested archive-recovery design. The example should first be validated in a nonproduction environment and then incorporated into an approved production runbook.

Authorization and Separation of Duties

AUDIT_ADMIN is the intended administrative role for audit-policy and audit-trail lifecycle operations. SYSDBA and AUDIT_ADMIN have execution authority on DBMS_AUDIT_MGMT by default, whereas AUDIT_VIEWER supports authorized evidence review without replacing the authority required for cleanup. Application accounts should not receive audit-administration privileges merely so their records can be purged.

Oracle Database Vault can impose additional authorization on protected audit operations and objects. Limit package access to designated audit administrators and preserve separation between the people who generate activity, review evidence, approve retention decisions, and execute cleanup. Calls to DBMS_AUDIT_MGMT are themselves mandatorily audited, keeping the administrative cleanup activity attributable.

Safely Purge Records Older Than 30 Days

1. Confirm the Current Container and Retention Decision

The primary example operates in the pluggable database whose unified trail is being managed. Confirm the current container before changing any cleanup state:

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM   dual;

The administrator must also confirm the approved retention period, any investigation or legal hold, the archive destination and protection requirements, the change record, the expected volume, and the maintenance window. Determine whether Oracle Audit Vault and Database Firewall, Oracle Data Safe, or another approved collector already controls collection, archival, and archive-timestamp management. Do not create a competing local process when an external system owns that lifecycle.

This record separates the business decision to dispose of evidence from the technical ability to call the package. A successful PL/SQL block proves only that Oracle accepted the operation; it does not prove that the retention decision, archive, or legal authorization was correct.

2. Estimate the Eligible Records

Use a bounded UTC expression to estimate the records older than the illustrative cutoff:

SELECT COUNT(*)                 AS purge_candidate_count,
       MIN(event_timestamp_utc) AS oldest_candidate_utc,
       MAX(event_timestamp_utc) AS newest_candidate_utc
FROM   unified_audit_trail
WHERE  event_timestamp_utc <
       SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY;

This aggregate is a planning estimate, not authorization to delete. The count can change while database activity continues. Avoid selecting SQL_TEXT or SQL_BINDS merely for sizing because those fields can contain sensitive values. For a large trail, assess the expected work, redo generation, and maintenance-window impact before cleanup begins.

3. Archive and Verify the Evidence

Move evidence that must be retained to an approved protected repository. A secure archive may be managed by Oracle Audit Vault and Database Firewall, Oracle Data Safe where applicable, or another validated export and retention process. A simple table copy is not, by itself, proof of a complete or protected archive.

Before declaring an interval purgeable, verify the source database and exact UTC interval, reconcile record counts or an equivalent control, confirm that the archive is readable and recoverable, protect its integrity and access, and retain identifiers such as DBID, DB_UNIQUE_NAME, and the container identity. Never move the last archive timestamp beyond the latest record that has been durably archived and verified.

4. Set and Commit the UTC Archive Boundary

After the archive has been verified, set the last archive timestamp for the current PDB:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
      audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
      last_archive_time => SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY,
      container         => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

COMMIT;

AUDIT_TRAIL_UNIFIED selects the unified trail. SYS_EXTRACT_UTC(SYSTIMESTAMP) creates the cutoff in the UTC basis required for unified auditing instead of relying on the host or session time zone. CONTAINER_CURRENT restricts the change to the connected root or PDB. Oracle rejects a future timestamp, but the administrator remains responsible for proving that the chosen past cutoff is safe.

SET_LAST_ARCHIVE_TIMESTAMP records a boundary; it neither copies nor validates audit evidence. Keep this operation and the purge operation in separate transaction blocks. The explicit COMMIT completes the timestamp-setting transaction before cleanup begins and avoids unpredictable results.

5. Verify the Stored Cutoff

In a read-write database, inspect the configured archive boundary before using it:

SELECT audit_trail,
       last_archive_ts,
       database_id,
       container_guid,
       db_unique_name
FROM   dba_audit_mgmt_last_arch_ts
WHERE  audit_trail = 'UNIFIED AUDIT TRAIL'
ORDER  BY last_archive_ts DESC;

Confirm that the timestamp belongs to the intended database and container and does not exceed the verified archive interval. A row's existence is not enough. The DATABASE_ID, CONTAINER_GUID, and DB_UNIQUE_NAME values help distinguish records after cloning, relocation, or a Data Guard role transition.

6. Run the Manual Purge

Run the supported cleanup operation with the archive boundary enforced:

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
      audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
      use_last_arch_timestamp => TRUE,
      container               => DBMS_AUDIT_MGMT.CONTAINER_CURRENT,
      drop_partition_only     => FALSE);
END;
/

With use_last_arch_timestamp => TRUE, only records created before the verified last archive timestamp are eligible. The main example keeps drop_partition_only => FALSE so cleanup can remove all eligible records, including rows in a partition that cannot be dropped as a complete unit. This procedure does not authorize deletion beyond the retention decision that preceded it.

Setting use_last_arch_timestamp to FALSE ignores the stored cutoff and makes every record in the selected audit trail eligible. That behavior is outside the normal retention workflow and requires exceptional, separately approved handling; it is intentionally not shown as executable code.

7. Verify the Purge Result

Verify the intended interval against the stored boundary rather than recalculating a later rolling cutoff:

SELECT COUNT(*)                 AS records_before_cutoff,
       MIN(event_timestamp_utc) AS oldest_remaining_utc,
       MAX(event_timestamp_utc) AS newest_remaining_utc
FROM   unified_audit_trail
WHERE  event_timestamp_utc < (
         SELECT SYS_EXTRACT_UTC(MAX(last_archive_ts))
         FROM   dba_audit_mgmt_last_arch_ts
         WHERE  audit_trail = 'UNIFIED AUDIT TRAIL');

Investigate any unexpected remaining records in the context of database identity, container scope, and internal partition state. Do not test success by expecting the entire unified trail to be empty: the purge call is audited, and concurrent database activity can add records. Logical record removal also does not guarantee that a filesystem or tablespace immediately shrinks by a corresponding amount.

Schedule Recurring Cleanup

After the manual process has been tested and approved, create a daily purge job in the current PDB:

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
      audit_trail_purge_interval => 24,
      audit_trail_purge_name     => 'UNIFIED_AUDIT_PURGE_DAILY',
      use_last_arch_timestamp    => TRUE,
      container                  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

The interval is measured in hours and begins when the job is created. The job periodically invokes CLEAN_AUDIT_TRAIL; it does not archive audit records and does not advance a static last archive timestamp. A rolling 30-day policy therefore requires a separate verified archive cycle to advance the timestamp after each successful archive. If the cutoff becomes stale, the purge boundary also becomes stale.

For example, suppose the archive timestamp is set once and the daily purge job continues for several months. Each run still evaluates the same stored boundary. Newer records do not become eligible merely because they have aged past 30 days. The archive workflow must collect and verify the next interval and then deliberately advance the timestamp. Monitor the age of the stored cutoff as well as the job status so an enabled job is not mistaken for a functioning rolling-retention process.

Verify the configured job through the management view:

SELECT job_name,
       job_status,
       audit_trail,
       job_frequency,
       use_last_archive_timestamp,
       job_container
FROM   dba_audit_mgmt_cleanup_jobs
WHERE  job_name = 'UNIFIED_AUDIT_PURGE_DAILY';

Confirm that JOB_STATUS has the intended state, AUDIT_TRAIL identifies the unified trail, USE_LAST_ARCHIVE_TIMESTAMP is YES, JOB_CONTAINER is CURRENT, and JOB_FREQUENCY matches the approved schedule. Use SET_PURGE_JOB_INTERVAL to change the interval, SET_PURGE_JOB_STATUS to disable or re-enable the job, and DROP_PURGE_JOB to remove it. Disabling a purge job stops cleanup execution; it does not disable an audit policy or stop evidence collection.

Account for Multitenant and Advanced Environments

• PDB and CDB scope: A CONTAINER_CURRENT operation in a PDB applies only to that PDB; in the root, it applies only to the root. CONTAINER_ALL is a deliberate root-level design. A global job that honors archive timestamps needs a valid, verified cutoff for every affected PDB; one PDB's archive does not prove that all PDBs are ready.
• Oracle RAC and Data Guard: Synchronize RAC node clocks with NTP and omit rac_instance_number for the unified trail workflow. Verify DB_UNIQUE_NAME after Data Guard role changes so an obsolete database identity does not silently define the cleanup boundary.
• Read-only databases: DBA_AUDIT_MGMT_LAST_ARCH_TS is not populated by the timestamp-setting call in read-only mode; use DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP to verify the configured value. Scheduler-based purge jobs do not run in a database or PDB open read only, and cleanup of a read-only PDB's internal unified table is not performed.
• Large trails: drop_partition_only => TRUE can improve cleanup performance by dropping only complete eligible internal partitions and skipping row deletion. Older rows can remain in the current partition, so this option changes the expected verification result. Treat partition interval and capacity tuning as release-specific operational decisions; do not issue direct DDL against AUDSYS storage.

Upgraded databases can retain traditional, XML, operating-system, or pre-12.2 audit artifacts, and DBMS_AUDIT_MGMT retains facilities for those migration cases. They are not the Oracle 26ai unified-auditing workflow. Inventory such artifacts and follow the current Oracle Upgrade Guide and Security Guide rather than applying direct table DML, shell deletion, deprecated cleanup-initialization procedures, or obsolete audit initialization parameters.

Complete the Evidence Lifecycle

Safe audit cleanup is an evidence-lifecycle process: retain records for the approved period, archive and verify what must be preserved, set the UTC boundary only after that verification, and purge through DBMS_AUDIT_MGMT with the boundary enforced. Monitor both the archive cycle and the purge job; either one can become stale independently. With that distinction established, the next lesson concludes the module's examination of database auditing.