Oracle Tracing Activity of Triggers

oracleplsql

Our legacy application has over 4000+ triggers.

In order to diagnose a bug, we have to trace which trigger(s) inserted records into a particular table.

Had the legacy triggers logged their activity into a common table, we could have easily traced the activity on a particular table.

But this is not how the triggers were coded.

Is there a way to setup some kind of auditing process to trace following activity on the table :

 a. which trigger inserted record into the table
 b. what was the statement
 c. when the activity took place.

Since this is a development environment – we are not too concerned about performance implications.

thanks all.

Best Answer

Sure.

Enable auditing as:

alter system set audit_trail=db,extended scope=spfile;

Restart the database for the above to take effect.

Then enable auditing on the table:

audit insert on schema.table by access;

Then view the audit records in DBA_AUDIT_TRAIL. This gives you b. and c.. Using SQL_TEXT, you can find the SQL in V$SQL, and in V$SQL, the PROGRAM_ID (=OBJECT_ID in DBA_OBJECTS) and PROGRAM_LINE# columns tell you where that statement came from.

Related Question