oracle_audit_tracing

This is an old revision of the document!


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

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;
/

  • oracle_audit_tracing.1565357496.txt.gz
  • Last modified: 2019/10/18 20:04
  • (external edit)