Auditing

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 ?

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.

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

CREATE TABLE t1 ( x int ); 
 
audit INSERT ON t1 BY access;

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