A Practical Guide for DBAs
Auditing helps Oracle DBAs track who did what, when, and from where. Whether for security reviews, compliance, or incident investigations, having ready-to-use audit queries saves time and avoids guesswork.
Oracle supports Standard Auditing and Unified Auditing. This blog covers both.
1. Check Which Auditing Is Enabled
SELECT value
FROM v$option
WHERE parameter = ‘Unified Auditing’;
- TRUE → Unified Auditing enabled
- FALSE → Standard Auditing
2. Standard Audit Trail Queries (DBA_AUDIT_TRAIL)
🔹 Login / Logout Activity
SELECT
username,
userhost,
action_name,
timestamp
FROM dba_audit_trail
WHERE action_name IN (‘LOGON’, ‘LOGOFF’)
ORDER BY timestamp DESC;
🔹 Failed Login Attempts
SELECT
username,
userhost,
returncode,
timestamp
FROM dba_audit_trail
WHERE returncode != 0
ORDER BY timestamp DESC;
🔹 DDL Changes (CREATE / DROP / ALTER)
SELECT
username,
obj_name,
action_name,
timestamp
FROM dba_audit_trail
WHERE action_name IN (‘CREATE’, ‘DROP’, ‘ALTER’)
ORDER BY timestamp DESC;
🔹 Privilege & Role Changes
SELECT
username,
action_name,
timestamp
FROM dba_audit_trail
WHERE action_name LIKE ‘%GRANT%’
OR action_name LIKE ‘%REVOKE%’
ORDER BY timestamp DESC;
3. Unified Audit Trail Queries (UNIFIED_AUDIT_TRAIL)
🔹 All Audit Records
SELECT
dbusername,
action_name,
object_name,
event_timestamp
FROM unified_audit_trail
ORDER BY event_timestamp DESC;
🔹 Login Activity (Unified)
SELECT
dbusername,
userhost,
action_name,
event_timestamp
FROM unified_audit_trail
WHERE action_name = ‘LOGON’
ORDER BY event_timestamp DESC;
🔹 Failed Logins (Unified)
SELECT
dbusername,
userhost,
return_code,
event_timestamp
FROM unified_audit_trail
WHERE return_code != 0
ORDER BY event_timestamp DESC;
🔹 Object Changes (DDL)
SELECT
dbusername,
action_name,
object_schema,
object_name,
event_timestamp
FROM unified_audit_trail
WHERE action_name IN (‘CREATE TABLE’,’DROP TABLE’,’ALTER TABLE’)
ORDER BY event_timestamp DESC;
🔹 Privilege & Role Grants
SELECT
dbusername,
action_name,
system_privilege,
event_timestamp
FROM unified_audit_trail
WHERE action_name IN (‘GRANT’,’REVOKE’)
ORDER BY event_timestamp DESC;
4.⃣ Who Accessed a Specific Table
SELECT
dbusername,
action_name,
object_schema,
object_name,
event_timestamp
FROM unified_audit_trail
WHERE object_name = ‘EMPLOYEES’
ORDER BY event_timestamp DESC;
5.⃣ Audit Policy Verification (Unified Auditing)
SELECT policy_name, enabled_option
FROM audit_unified_enabled_policies;
6.⃣ Audit Trail Size & Cleanup
🔹 Audit Trail Size
SELECT
segment_name,
bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE segment_name LIKE ‘%AUD%’;
🔹 Last Audit Record Timestamp
SELECT MAX(event_timestamp)
FROM unified_audit_trail;
