Oracle Database - Auditing

About

To answer this questions :

• How can we find out who dropped this table
• How people actually use your application or how they abuse it
• How to identify the DDL actions performed the last 24 hours ?
• If a developer overwrite some function, how can I tell who did it ?

To give you useful information over time :

• Usage patterns
• What's or not popular ?

Articles Related

How to enable auditing ?

Just choose the level of audit (every page view in the website or an insert into a table) and install :

• a Basic Auditing using the built-in features of the database
• or a Custom Auditing using the system event trigger (BEFORE CREATE, BEFORE DROP and so, on)

Take in memory that the built-in auditing will go always faster than the custom one.

Built-in Audit

After making sure AUDIT_TRAIL was set in the ini.ora parameter file.

create table t1 ( x int ); 

audit insert on t1 by access;

Custom Auditing

create table t2 ( x int );

create table t2_audit
as
select sysdate dt, a.*
  from v$session a
 where 1=0;

create index t2_audit_idx on t2_audit(sid,serial#);

create trigger t2_audit
after insert on t2
begin
  insert into t2_audit
  select sysdate, a.*
    from v$session a
   where sid = ( select sid from V$mystat where rownum = 1 );
end;
/