I have been an Oracle DBA for over 14 years, but have only had some minor experience with DML auditing. I have been tasked to implement a monstrous audit trail. Much to my dismay, all of the 280+ tables in the current schema have 4 audit columns, the create user and date, and the update user and date. In addition, each table has an audit trail counterpart table, populated via triggers, and has an additional 5 columns: the operation (update, delete, etc) and time, the application name, the OS user, and terminal. In addition, there is a reporting requirement that will require highly complex views on the audit tables, in some cases requiring joins to the production tables, and in some cases joining 8 or 9 tables to pull in a "consistent picture" of audit data for some "entity" and its related tables. Aarrghhhh.

A few years back, I saw a presentation on creating an audit trail of all DML in a single table. This would include rows for inserts, updates, and deletes for each table in the database. In addition, there would be a provision for seeing before and after values for updates. I am trying to push such a solution for this case, but would like to hear from anyone who has used or is using a single table for auditing on this scale. The (very preliminary) picture I have floating around in my coconut is a huge partitioned table (by month). Each row would have the table name, column name(s), PK value,(every table has a Primary Key) before and after values, user, transaction (update, delete) timestamps, etc. My thought is that if everything can be put in one such table, while it would be large, it would actually be smaller than the total of 280+ separate audit tables. In addition, the reports could be satisfied by creating the required views on the audit table only, and never have to join to production (performance!). In this way, if there are schema changes in production, instead of having to make the same changes in the audit tables, only the views would have to be recompiled. We would only keep 6 months of audit trail online (I am trying to talk them into 3) and age out older partitions (exporting them first). I will appreciate any suggestions, including the name of anyone who can supply me with hemlock.

-- Mike Larkin (Michael_Larkin@compaid.com), September 08, 2003