Unified Auditing

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Intermediate
Unified Auditing
Intermediate Topic 3 of 6

Why Audit?

Auditing records who did what and when. It answers questions like: Who dropped that table? Who tried to guess the password? Who read the salary data? Many regulations (finance, healthcare) require it.

Unified Audit

Modern Oracle uses unified auditing: one audit trail, one set of policies and one view called UNIFIED_AUDIT_TRAIL. It replaces the old scattered audit options.

sql SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

Create and Enable a Policy

sql CREATE AUDIT POLICY audit_ddl ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE; AUDIT POLICY audit_ddl;
sql -- audit access to a sensitive table CREATE AUDIT POLICY audit_orders_access ACTIONS SELECT ON shop.orders, UPDATE ON shop.orders, DELETE ON shop.orders; AUDIT POLICY audit_orders_access;

Use the Oracle-Supplied Policies

PolicyWhat it audits
ORA_LOGON_FAILURESFailed logins
ORA_SECURECONFIGA recommended set of security-sensitive actions
ORA_ACCOUNT_MGMTCreating, altering, dropping users and roles
sql AUDIT POLICY ORA_LOGON_FAILURES; AUDIT POLICY ORA_ACCOUNT_MGMT;

Audit Only Some Users

sql AUDIT POLICY audit_ddl BY shop, report_user; AUDIT POLICY audit_ddl EXCEPT backup_svc; AUDIT POLICY audit_orders_access WHENEVER NOT SUCCESSFUL;

Read the Audit Trail

sql SELECT event_timestamp, dbusername, action_name, object_schema, object_name, return_code, client_program_name FROM unified_audit_trail WHERE event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY ORDER BY event_timestamp DESC FETCH FIRST 20 ROWS ONLY;
Return code: return_code = 0 means success. Any other number is an ORA error, for example 1017 means a wrong password.

See Which Policies Are On

sql SELECT policy_name, enabled_option, entity_name FROM audit_unified_enabled_policies;

Stop Auditing

sql NOAUDIT POLICY audit_ddl; DROP AUDIT POLICY audit_ddl;

Keep the Trail from Filling the Database

Audit records are stored in the SYSAUX tablespace. Purge old data on a schedule:

sql BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE); END; /
Best practice: Export audit records to a central store or SIEM first, then purge. And do not audit everything: audit what you will really read.

Conclusion

Use unified auditing to record logins, account changes, DDL and access to sensitive data. Read UNIFIED_AUDIT_TRAIL and purge old records on a schedule.

Quick Summary: CREATE AUDIT POLICY, AUDIT POLICY, read UNIFIED_AUDIT_TRAIL, purge with DBMS_AUDIT_MGMT.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators