Tracking operation on Specific Schema object ** Audit **

Oracle build in Auditing feature to Track the occurrence of SQL statements in subsequent user sessions You can track the occurrence of a specific SQL statement or of all SQL statements authorized by a particular system privilege. ( To audit occurrences of a SQL statement audit_trail should be set in init.ora )


rajesh@ORCL> select name,value,display_value
  2  from v$parameter
  3  where name = 'audit_trail'
  4  /

NAME                 VALUE      DISPLAY_VA
-------------------- ---------- ----------
audit_trail          DB         DB

Elapsed: 00:00:00.00
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> create table t1(x number);

Table created.

Elapsed: 00:00:02.07
rajesh@ORCL> audit insert on t by access;

Audit succeeded.

Elapsed: 00:00:00.21
rajesh@ORCL>
rajesh@ORCL> create table t1_timing(x timestamp,y timestamp);

Table created.

Elapsed: 00:00:00.14
rajesh@ORCL>
rajesh@ORCL> create or replace procedure audit_test (p_limit in number)
  2  as
  3     p_rowid rowid;
  4  begin
  5     insert into t1_timing(x) values(systimestamp) returning rowid into p_rowid;
  6
  7     for i in 1..p_limit
  8     loop
  9             insert into t1 values (i);
 10             commit;
 11     end loop;
 12
 13     update t1_timing set y = systimestamp where rowid = p_rowid;
 14     commit;
 15  end;
 16  /

Procedure created.

Elapsed: 00:00:04.95
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> exec audit_test(30000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.15

rajesh@ORCL> select y - x from t1_timing;

Y-X
---------------------------------------------------------------------------
+000000000 00:00:02.609000

Elapsed: 00:00:00.01

Now the same approach using " do it yourself " auditing

rajesh@ORCL> create table t2(x number);

Table created.

Elapsed: 00:00:00.39
rajesh@ORCL> create table t2_audit as
  2  select sysdate as dt,
  3        a.*
  4  from v$session a
  5  where 1 = 0;

Table created.

Elapsed: 00:00:00.14
rajesh@ORCL> create or replace trigger t2_audit_trig
  2  after insert on t2
  3  for each row
  4  begin
  5     insert into t2_audit
  6     select sysdate as dt,
  7                     a.*
  8     from v$session a
  9     where sid = userenv('sid');
 10  end;
 11  /

Trigger created.

Elapsed: 00:00:00.21
rajesh@ORCL>
rajesh@ORCL> truncate table t1_timing;

Table truncated.

Elapsed: 00:00:01.96
rajesh@ORCL> create or replace procedure audit_test (p_limit in number)
  2  as
  3     p_rowid rowid;
  4  begin
  5     insert into t1_timing(x) values(systimestamp) returning rowid into p_rowid;
  6
  7     for i in 1..p_limit
  8     loop
  9             insert into t2 values (i);
 10             commit;
 11     end loop;
 12
 13     update t1_timing set y = systimestamp where rowid = p_rowid;
 14     commit;
 15  end;
 16  /

Procedure created.

Elapsed: 00:00:00.35
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> exec audit_test(30000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.35
rajesh@ORCL> select y - x from t1_timing;

Y-X
---------------------------------------------------------------------------
+000000000 00:00:44.359000

Elapsed: 00:00:00.00
rajesh@ORCL>

Even for this simple example, results are clear. Its easier, faster and all around more efficient to use native functionality