A single wrong query can sink days of work. With Sqlplus, the only evidence of what happened lives in your audit logs.
Audit logs in Sqlplus are your black box recorder. They track every session, every command, every login, and every action tied to a database user. Without them, tracing suspicious activity or recovering from an unexpected change becomes guesswork. With them, you have the facts.
To enable audit logs in Sqlplus, connect as a privileged user and turn on auditing at the database level. Use commands like:
AUDIT SESSION;
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY ACCESS;
These statements log user sessions and DML operations. You can be specific—auditing only certain tables or all of them. For heightened security, audit connections both successful and failed:
AUDIT CONNECT;
The logs themselves are stored in DBA_AUDIT_TRAIL, DBA_AUDIT_SESSION, and related tables. Querying them gives you a timeline of events down to the second:
SELECT username, timestamp, action_name, obj_name
FROM dba_audit_trail
ORDER BY timestamp DESC;
Keep retention in mind. Old logs can balloon storage and slow down queries. Archive them regularly into file-based audit logs or a centralized logging system. Secure them tightly—never let those logs sit in a writable location.
For production systems, enable AUDIT_TRAIL=db,extended in the database initialization parameter file to capture both the command issued and bind variables. Restart the database to apply changes. This level of detail makes intrusion detection and debugging precise.
Audit logs also help with compliance. Regulations like SOX, HIPAA, and GDPR often demand evidence of access patterns. By keeping your Sqlplus audit logs accurate, complete, and readable, you can pass audits without scrambling.
The best setups combine native Sqlplus auditing with real-time log forwarding to external monitoring. This reduces the risk of tampering and helps you respond before damage spreads.
If you want to see audit logging, filtering, and alerting in action—without writing a single script—try it on hoop.dev. Spin it up, connect your database, and watch real audit logs stream live in minutes.