Auditing & Compliance

The EXA_DBA_AUDIT_* tables provide a full audit trail of SQL execution and user sessions. They are designed for security investigations, compliance reporting, and operational support.

Note

All tables in this section require the SELECT ANY DICTIONARY system privilege. See Access Control for details on granting this privilege.

EXA_DBA_AUDIT_SQL

Records every SQL statement executed in the database. Key columns:

Column	Description
USER_NAME	Database user who executed the statement
SESSION_ID	Session identifier
STMT_ID	Statement identifier within the session
COMMAND_NAME	SQL command type (e.g., SELECT, CREATE TABLE)
COMMAND_CLASS	Broad category: DQL, DML, DDL, DCL, TCL
SQL_TEXT	Full SQL text (up to 2,000,000 characters)
SUCCESS	TRUE if the statement completed without error
ERROR_CODE / ERROR_TEXT	Error details for failed statements
DURATION	Execution time in seconds
STMT_START_TIME	Timestamp when the statement began executing

EXA_DBA_AUDIT_SESSIONS

Records every database session, including login and logout events. Key columns:

Column	Description
SESSION_ID	Unique session identifier
USER_NAME	Database user who opened the session
OS_USER	Operating system user on the client machine
HOST	Client host address
LOGIN_TIME	Session start timestamp
LOGOUT_TIME	Session end timestamp (NULL if session is still active)
SUCCESS	TRUE for successful logins; FALSE for failed login attempts
ENCRYPTED	TRUE if the connection was encrypted

Managing Audit Log Size

Audit tables grow continuously. Remove old records while retaining recent history with TRUNCATE AUDIT LOGS:

-- Keep the last 30 days; remove everything older
TRUNCATE AUDIT LOGS KEEP FROM DAYS=30;

Warning

TRUNCATE AUDIT LOGS permanently deletes the removed records. This action cannot be undone.

Recipes

Find All Failed Statements with Error Details

SELECT USER_NAME, COMMAND_NAME, SQL_TEXT,
       ERROR_CODE, ERROR_TEXT, STMT_START_TIME
FROM EXA_DBA_AUDIT_SQL
WHERE SUCCESS = FALSE
ORDER BY STMT_START_TIME DESC
LIMIT 50;

Track Login History for a Specific User

SELECT SESSION_ID, LOGIN_TIME, LOGOUT_TIME,
       HOST, OS_USER, ENCRYPTED
FROM EXA_DBA_AUDIT_SESSIONS
WHERE USER_NAME = 'MY_USER'
ORDER BY LOGIN_TIME DESC;

List All DDL Statements Executed Today

SELECT USER_NAME, COMMAND_NAME, SQL_TEXT, STMT_START_TIME
FROM EXA_DBA_AUDIT_SQL
WHERE COMMAND_CLASS = 'DDL'
  AND CAST(STMT_START_TIME AS DATE) = CURRENT_DATE
ORDER BY STMT_START_TIME DESC;

Remove Audit Logs Older Than 30 Days

TRUNCATE AUDIT LOGS KEEP FROM DAYS=30;

Further reading: EXA_DBA_AUDIT_SQL · EXA_DBA_AUDIT_SESSIONS · Auditing Concepts