DBA_AUDIT_TRAIL - Query Oracle Traditional Audit Records

DBA_AUDIT_TRAIL

Overview

DBA_AUDIT_TRAIL exposes the contents of the traditional (standard) audit trail stored in the SYS.AUD$ table. When database auditing is enabled via the AUDIT_TRAIL initialisation parameter, Oracle writes a record to this view for every audited action: successful and failed logins, DDL events such as CREATE or DROP, DML on audited objects, and privilege usage. DBAs and security teams query it to investigate suspicious activity, produce compliance evidence, and correlate events around a known incident time window. For databases using Unified Auditing (Oracle 12c+), see UNIFIED_AUDIT_TRAIL instead.

View Type: Data Dictionary View (exposes SYS.AUD$) Available Since: Oracle 7 Required Privileges: SELECT on DBA_AUDIT_TRAIL or AUDIT_ADMIN role or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OS_USERNAME	VARCHAR2(255)	Operating system username of the client session
USERNAME	VARCHAR2(128)	Oracle database username
USERHOST	VARCHAR2(128)	Client machine name or IP address
TERMINAL	VARCHAR2(255)	Terminal identifier of the client
TIMESTAMP	DATE	Date and time the audited action occurred
OWNER	VARCHAR2(128)	Schema of the object involved in the audited action
OBJ_NAME	VARCHAR2(128)	Name of the object involved (table, procedure, etc.)
ACTION	NUMBER	Numeric code of the SQL action performed
ACTION_NAME	VARCHAR2(28)	Text description of the action (SELECT, INSERT, CREATE TABLE, etc.)
NEW_OWNER	VARCHAR2(128)	New schema owner after a rename or move
NEW_NAME	VARCHAR2(128)	New object name after a rename
OBJ_PRIVILEGE	VARCHAR2(16)	Object privilege involved when auditing privilege use
SYS_PRIVILEGE	VARCHAR2(40)	System privilege involved when auditing system privilege use
ADMIN_OPTION	VARCHAR2(1)	Y if the grant was made WITH ADMIN OPTION
GRANTEE	VARCHAR2(128)	Grantee in a GRANT or REVOKE statement
AUDIT_OPTION	VARCHAR2(40)	Audit option that triggered this record
SES_ACTIONS	VARCHAR2(19)	Session summary of S/F (Success/Failure) per action type
SESSIONID	NUMBER	Numeric session identifier
ENTRYID	NUMBER	Numeric position within the session’s audit trail entries
STATEMENTID	NUMBER	Numeric statement identifier within the session
RETURNCODE	NUMBER	Oracle error code returned by the audited statement (0 = success)
PRIV_USED	VARCHAR2(40)	System privilege actually used to execute the statement
CLIENT_ID	VARCHAR2(64)	Client identifier set by DBMS_APPLICATION_INFO.SET_CLIENT_INFO
ECONTEXT_ID	VARCHAR2(64)	Execution context ID for Fine-Grained Auditing
SESSION_CPU	NUMBER	CPU time consumed by the session
SQL_TEXT	CLOB	Full SQL text of the audited statement (when AUDIT_TRAIL includes DB_EXTENDED)
SQL_BIND	CLOB	Bind variable values at the time of execution (when DB_EXTENDED)

Essential Queries

Basic Usage

Show recent audit records for a specific user, ordered by time:

SELECT
    timestamp,
    username,
    os_username,
    userhost,
    action_name,
    owner,
    obj_name,
    returncode,
    priv_used
FROM
    dba_audit_trail
WHERE
    username = 'APP_USER'
    AND timestamp > SYSDATE - 7
ORDER BY
    timestamp DESC;

Monitoring Query

Find all failed login attempts in the last 24 hours — a key indicator of brute-force activity or misconfigured application connection pools:

SELECT
    timestamp,
    username,
    userhost,
    terminal,
    os_username,
    returncode,
    CASE returncode
        WHEN 1017 THEN 'Invalid username/password'
        WHEN 28000 THEN 'Account locked'
        WHEN 28001 THEN 'Password expired'
        WHEN 1045 THEN 'No CREATE SESSION privilege'
        ELSE 'Error: ' || returncode
    END                                         AS failure_reason
FROM
    dba_audit_trail
WHERE
    action_name = 'LOGON'
    AND returncode != 0
    AND timestamp > SYSDATE - 1
ORDER BY
    timestamp DESC;

Combined with Other Views

Correlate audit records for DDL events with the current object definition in the data dictionary to show what was changed and by whom:

SELECT
    a.timestamp,
    a.username,
    a.action_name,
    a.owner,
    a.obj_name,
    a.returncode,
    o.object_type,
    o.status                                    AS current_status,
    o.last_ddl_time
FROM
    dba_audit_trail a
    LEFT JOIN dba_objects o
        ON  o.owner       = a.owner
        AND o.object_name = a.obj_name
WHERE
    a.action_name IN (
        'CREATE TABLE', 'ALTER TABLE', 'DROP TABLE',
        'CREATE PROCEDURE', 'ALTER PROCEDURE', 'DROP PROCEDURE',
        'CREATE INDEX', 'DROP INDEX'
    )
    AND a.timestamp > SYSDATE - 30
ORDER BY
    a.timestamp DESC;

Privileged Action Audit

Show all actions performed using powerful system privileges — ALTER DATABASE, ALTER SYSTEM, DROP USER — by non-Oracle accounts:

SELECT
    timestamp,
    username,
    userhost,
    os_username,
    action_name,
    priv_used,
    sys_privilege,
    obj_name,
    returncode
FROM
    dba_audit_trail
WHERE
    priv_used IN (
        'ALTER DATABASE', 'ALTER SYSTEM', 'DROP USER',
        'CREATE USER', 'GRANT ANY PRIVILEGE', 'GRANT ANY ROLE',
        'SYSDBA', 'SYSOPER'
    )
    AND username NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
    AND timestamp > SYSDATE - 30
ORDER BY
    timestamp DESC;

Audit Trail Volume Report

Show audit record counts by day and action type to identify spikes that may indicate unusual activity:

SELECT
    TRUNC(timestamp, 'DD')                      AS audit_day,
    action_name,
    COUNT(*)                                    AS record_count,
    COUNT(CASE WHEN returncode != 0 THEN 1 END) AS failure_count,
    COUNT(DISTINCT username)                    AS distinct_users
FROM
    dba_audit_trail
WHERE
    timestamp > SYSDATE - 30
GROUP BY
    TRUNC(timestamp, 'DD'),
    action_name
ORDER BY
    audit_day DESC,
    record_count DESC;

Common Use Cases

• Login failure investigation — Filter on ACTION_NAME = ‘LOGON’ AND RETURNCODE != 0 to find accounts under brute-force attack or application connection pool misconfiguration causing ORA-01017 floods
• Pre- and post-change verification — When a change window ends, query DBA_AUDIT_TRAIL for the change window timeframe to confirm only authorised DDL was executed and by expected accounts
• Compliance evidence production — PCI-DSS Requirement 10 mandates audit trails for all privileged actions; export DBA_AUDIT_TRAIL records for the audit period as evidence
• Incident forensics — After a suspected data breach, filter by a specific table name in OBJ_NAME with ACTION_NAME = ‘SELECT’ to determine who accessed sensitive data and when
• AUD$ growth management — DBA_AUDIT_TRAIL is a view on SYS.AUD$; monitor its size and purge old records using DBMS_AUDIT_MGMT to prevent uncontrolled tablespace growth in SYSTEM
• Privilege usage tracking — The PRIV_USED column shows which system privilege the user actually exercised; this is useful for identifying privileges that are granted but never used, informing revocation decisions

Related Views

• DBA_USERS — Account details for USERNAME values found in audit records
• DBA_SYS_PRIVS — Confirm whether PRIV_USED values seen in the audit trail are still granted
• UNIFIED_AUDIT_TRAIL — The modern unified audit trail (12c+); recommended for new databases; richer detail and better performance than DBA_AUDIT_TRAIL
• DBA_AUDIT_POLICIES — Fine-Grained Audit (FGA) policies; FGA records appear in DBA_FGA_AUDIT_TRAIL, not in DBA_AUDIT_TRAIL
• DBA_STMT_AUDIT_OPTS — Shows which statement-level audit options are currently active
• DBA_PRIV_AUDIT_OPTS — Shows which privilege-level audit options are currently active

Version Notes

• Oracle 7: View introduced alongside the AUD$ table and AUDIT SQL statements
• Oracle 10g: SQL_TEXT and SQL_BIND CLOB columns added when AUDIT_TRAIL = DB_EXTENDED; CLIENT_ID added for application-level correlation
• Oracle 11g: AUDIT_TRAIL = XML and XML_EXTENDED options introduced, writing audit data to OS XML files instead of AUD$
• Oracle 12c: Unified Auditing introduced as the preferred auditing architecture; traditional auditing deprecated but still functional. DBMS_AUDIT_MGMT package introduced for AUD$ lifecycle management
• Oracle 19c: Mixed-mode auditing (both traditional and unified) remains supported; Oracle recommends migrating to pure unified auditing
• Oracle 23ai: Traditional auditing (DBA_AUDIT_TRAIL) still supported for backward compatibility; new deployments should use UNIFIED_AUDIT_TRAIL exclusively