This is an old revision of the document!
Overview
Auditing in Oracle is controlled by the audit_trail. Audit trail can be:
- None = Doesn't store Audit data
- DB = Audit information is stored in the DB (aud$ and dba_audit_trail)
- OS = Audit Information is stored on the OS (Audit_dump_location)
- XML = Writes to the operating system audit record file in XML format. Prints all elements of the AuditRecord node
Auditing
Tracing
Tracing can be enabled on LOGON via trigger as follows:
Pre 12c
CREATE OR REPLACE TRIGGER USER_TRACE_TRG AFTER LOGON ON DATABASE BEGIN IF USER = 'USERNAME' THEN execute immediate 'alter session set timed_statistics=true'; execute immediate 'alter session set max_dump_file_size=unlimited'; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; /
Post 12c
create or replace trigger USERNAME_startsqltracing after logon on USERNAME.schema begin execute immediate 'alter session set timed_statistics=true'; execute immediate 'alter session set max_dump_file_size=unlimited'; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; end; /