How to query the SYS.AUD$ audit trail in Oracle

Working with the standard audit trail (AUDIT_TRAIL=DB or DB_EXTENDED). Actions are stored as ACTION# codes in SYS.AUD$; join to AUDIT_ACTIONS to translate. In 10gR1+, use NTIMESTAMP# for the timestamp (TIMESTAMP# is deprecated). If AUDIT_TRAIL=DB_EXTENDED, the SQL text is captured too — read it from DBA_AUDIT_TRAIL.

Common ACTION# codes to remember: 2=INSERT, 3=SELECT, 6=UPDATE, 7=DELETE, 15=ALTER TABLE, 51=CREATE USER, 43=ALTER USER, 53=DROP USER, 100=LOGON, 101=LOGOFF. Full list: SELECT action, name FROM audit_actions ORDER BY action.

Requires SELECT on SYS.AUD$ (SYS or DBA role). AUD$ can grow very large — always filter by NTIMESTAMP# and ACTION# to keep queries responsive; consider an index if you query it often.

-- List all audit action codes and names
SELECT action, name
FROM   audit_actions
ORDER BY action;

-- Filter AUD$ by ACTION# names and date range
SELECT *
FROM   sys.aud$
WHERE  ntimestamp# > SYSDATE - 7
AND    action# IN (
         SELECT action FROM audit_actions
         WHERE  name IN ('ALTER USER', 'CREATE USER', 'DROP USER')
       );

-- Who did UPDATE (ACTION# = 6)
SELECT sessionid, entryid, statement, userid, userhost, ntimestamp#,
       action#, returncode, obj$creator, obj$name, ses$actions,
       ses$tid, logoff$time, comment$text, clientid, user$guid,
       instance#, process#, xid, auditid, scn
FROM   sys.aud$
WHERE  action# = 6;

-- Who did DELETE (ACTION# = 7) against a specific schema
SELECT sessionid, entryid, statement, userid, userhost, ntimestamp#,
       action#, returncode, obj$creator, obj$name, ses$actions,
       clientid, instance#, process#, xid, auditid, scn
FROM   sys.aud$
WHERE  action#     = 7
AND    obj$creator = 'YOUR_SCHEMA';

-- Who did DELETE by a specific user within a date range
SELECT sessionid, entryid, statement, userid, userhost, ntimestamp#,
       action#, returncode, obj$creator, obj$name, xid, scn
FROM   sys.aud$
WHERE  action#      = 7
AND    userid       = 'YOUR_APP_USER'
AND    ntimestamp# >= TO_DATE('08-NOV-2020', 'DD-MON-YYYY')
AND    ntimestamp# <= TO_DATE('13-NOV-2020', 'DD-MON-YYYY');

-- Get the actual SQL command (only when AUDIT_TRAIL=DB_EXTENDED)
COL sql_text FORMAT a30
SELECT username, os_username, action_name, obj_name,
       TO_CHAR(timestamp, 'dd/mm/yyyy hh24:mi') AS time,
       sql_text
FROM   dba_audit_trail
WHERE  obj_name = 'YOUR_TABLE';

-- Count DBIDs and audit entries with earliest / latest timestamp
COL "Earliest" FORMAT a30
COL "Latest"   FORMAT a30
SELECT dbid,
       COUNT(*),
       MIN(ntimestamp#) AS "Earliest",
       MAX(ntimestamp#) AS "Latest"
FROM   sys.aud$
GROUP BY dbid;