Most of data in oracle Applications is quite important but some tables are very critical & sensitive . From security reason or Audit compliances you as an Apps DBA should know
– Who changed sensitive tables ?
– At what time data was changed ?
– What was value before & after change ?
If you wish to track this information in Oracle Applications, I am sure most of you will say this can achieved by enabling Audit Trail in Oracle Applications but you need to take care of following things w.r.t. Audit Trail in Apps
– Make sure you plan purging policy for Audit/Shadow tables which will be created during Audit trail
– Consider for performance overheads if Audit Trails is enabled on too many applications tables
– Better to disable Audit Trails during major Upgrades (Shadow tables may change during version upgrade so its better to archive before upgrade )
Steps to enable Auditing
Define Audit Installation (Security -> AuditTrail -> Install)
Define Audit Group (Security -> AuditTrail -> Groups)
Run Audit Trail Update Tables Report to Enable Auditing (Submit Concurrent Screen)
Few key points for Auditing
–Auditing in Apps can be done on Row by Row basis or Individual Column of a table
–If you change any of your definitions for auditing, you must rerun Audit Trail Update Tables
–“AuditTrail Update Tables” report creates shadow table with first 26 characters of table & appended by _A
Metalink Notes related to Auditing
105630.1 Setup & Usage
60828.1 Overview of Oracle Applications AuditTrails
69660.1 Understanding Data Auditing in Oracle Application Tables
If you wish to know about users accessing Apps, you can refer nicely written post by Fadi on his Post About “Connected users in Apps” .