Database auditing refers to the monitoring and logging of user activity. It is the most accurate record of any database activity. Auditing helps us keep track of what happened in the database, when it happened and who did it. It also allows us to track the use of privileges, activities of highly privileged users, access to sensitive data, actions performed on database objects and modifications made to database settings. This helps pinpoint when certain actions happened and could be useful for various security requirements. In certain industries, Auditing is also a regulatory obligation and enables businesses to comply with different regulations, such as General Data Protection Regulation (GDPR), Payment Card Industry (PCI), California Consumer Privacy Act (CCPA), and other privacy regulations.
Types of auditing in Oracle
There are various types of auditing in Oracle, some of which are no longer in force in recent database versions. They include:
- Standard Auditing: The standard in Oracle 11g and earlier (and supported as a deprecated feature in later versions), Traditional Auditing
requires configuring individual categories of database activity using separate initialization parameters. - Fine grained auditing: Allows administrators to establish granular, condition-based audit rules on specific table columns. It is designed to capture data access rather than just the execution of a command.
- Unified Auditing: Introduced in Oracle 12c and later, Unified Auditing is the modern standard for Oracle security.
Under each of these different types of auditing, there are various specific types of audits and/or audit policies that can be specified.
In oracle, audit data is stored in the audit trail.
What is the audit trail?
The audit trail in oracle refers to the set of tables that store the audit data. It is basically the audit data and metadata. The most common audit trail view is the UNIFIED_AUDIT_TRAIL view, which consolidates all Oracle Database audit data into one location and in a unified format. The Oracle Audit Trail plays a pivotal role in maintaining database security and ensuring regulatory compliance.
In this post we will focus mainly on unified auditing and the unified_audit_trail as these are the standards going forward.
What is new in 26ai?
Starting from oracle database 26ai, standard auditing has been de-supported and unified auditing is the default way to go. Legacy standard auditing commands like audit session will throw error messages.
Unified auditing & the Unified Audit Trail
Unified auditing (introduced in 12c) replaces standard auditing as the de facto audit mechanism in 26ai. It enables you to capture audit records and write audit records into a single consolidated unified audit trail. The unified audit trail table AUD$UNIFIED is a specialized table in the AUDSYS schema in the SYSAUX tablespace which allows only INSERT activity. Audit data is managed using the built-in audit data management DBMS_AUDIT_MGMT package. The audit data is made available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view for the end user.
Why do we need to purge the Unified Audit Trail?
As explained above, audit records are in a database table in a database schema. These audit records can quickly fill up (depending on the policies in place) and occupy a lot of database space. Also, depending on the requirements, there might not be a need for all of the audit history. Therefore, we need to purge the audit trail periodically to save storage space in the oracle database.
How to purge the audit trail
We can purge the audit trail using the DBMS_AUDIT_MGMT package. Additionally, using this package, we can create a purge job that deletes audit records older than a given timeframe at a specified frequency. Let’s see how we can create this purge job in the oracle database.
Querying the Audit Trail
We can use the unified_audit_trail view to query audit data in oracle. The below query shows the amount of audit data in our database and the date of the oldest record.
SELECT
count(*) no_of_audit_records,
to_char(min(event_timestamp),'DD-MON-YYYY') min_audit_date
FROM
unified_audit_trail
/
NO_OF_AUDIT_RECORDS MIN_AUDIT_DATE
------------------- --------------------
13905 05-JUL-2026The results above show that we have 13905 audit records going back to July 2026. However, what if we don’t need all this history perhaps 2 months or just 30 days worth is okay. We can create a purge job to delete data older than 30 days every time it runs.
Create Automatic purge job
Using the dbms_audit_mgmt.create_purge_job, procedure we can create the purge job described. The procedure requires the following inputs:
audit_trail_type: The audit trail typeaudit_trail_purge_interval: The Job frequency in hoursaudit_trail_purge_name: The job nameuse_last_arch_timestamp:TRUEorFALSEcontainer: eithercontainer_currentorcontainer_all.
First we specify the last_arch_timestamp to be used for the job using the SET_LAST_ARCHIVE_TIMESTAMP procedure. Oracle recommends setting this in every individual PDB to avoid the risk that uncollected audit records get deleted. We can set this purge to always purge data older than 30 days when it runs as shown:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => TRUNC(SYSTIMESTAMP) - INTERVAL '30' DAY
);
END;
/
PL/SQL procedure successfully completed.We then create the purge job and specify use_last_arch_timestamp to true and container to current:
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 168,
AUDIT_TRAIL_PURGE_NAME => 'PURGE_AUDIT',
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => 'CONTAINER_CURRENT'
);
END;
/
PL/SQL procedure successfully completed.This job runs every 7 days and deletes data older than the specified archive timestamp.
Viewing purge job details
We can view the job details using the dba_scheduler_jobs data dictionary view. The below query confirms the purge job exists:
col job_name for a20
col job_type for a20
col job_creator for a20
col start_date for a20
col end_date for a20
col repeat_interval for a25
col enabled for a20
col runtime for a20
set linesize 200
SELECT
job_name,
job_type,
job_creator,
to_char(last_start_date, 'DD-MON-RR HH24:MI:SS') AS last_run,
to_char(next_run_date, 'DD-MON-RR HH24:MI:SS') AS next_run,
repeat_interval,
enabled
FROM
dba_scheduler_jobs
WHERE
job_name = 'PURGE_AUDIT';
JOB_NAME JOB_TYPE JOB_CREATOR LAST_RUN NEXT_RUN REPEAT_INTERVAL ENABLED
-------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------- --------------------
PURGE_AUDIT PLSQL_BLOCK SYS 06-OCT-26 11:22:26 13-OCT-26 11:22:26 FREQ=HOURLY;INTERVAL=168 TRUE
After the purge job ran, the following query shows information about the data left in the audit trail:
SELECT
count(*) no_of_audit_records,
to_char(min(event_timestamp),'DD-MON-YYYY') min_audit_date
FROM
unified_audit_trail
/
NO_OF_AUDIT_RECORDS MIN_AUDIT_DATE
------------------- --------------------
5 23-SEP-2026As shown, the number of audit records have dropped to 5 and the oldest record is from September.
Drop Purge Job
The purge job can be dropped by using the drop_purge_job procedure as shown:
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
AUDIT_TRAIL_PURGE_NAME => 'PURGE_AUDIT');
END;
/
PL/SQL procedure successfully completed.Main takeaways
- Auditing in Oracle helps us keep track of all detailed database activity. For a detailed overview of auditing in oracle, see Introduction to Auditing.
- The audit trail refers to the records of audit data in the database.
- The audit trail can be purged using the
DBMS_AUDIT_MGMTpackage to save space. - The create_purge_job procedure of the
DBMS_AUDIT_MGMTpackage helps create jobs for deleting audit trail data. - There are lots of options to choose from when creating a purge job. (see details)
- Purge jobs can be dropped using the
drop_purge_jobprocedure.
PS: If you’re migrating to 26ai and have been using standard auditing, MOS note 2909718.1 provides information on converting those polices to unified auditing.
Hope you found this helpful and thanks for reading.
Cheers, Harris.