11gR2 - auditing row changes

From: Jeremy <jeremy0505_at_gmail.com>
Date: Sun, 18 Aug 2013 12:20:29 +0100
Message-ID: <MPG.2c7abe5a59d25319989720_at_news.individual.net>

Hi we are running 11gR2 standard edition.

A business requirement has emerged which means that potentially a user would like to be able to see what columns have cheanged on each update to a row in one of several tables.

Is there something standard within Oracle to set this kind of auditing on - and if so, are the results queryable?

Assume we had a table with 5 rows like this

ID col1 col2 col3 col4 col5 col6 date_time

121  A      A      B      C     D     E       21/08/13 18:22:10
121  A      B      B      C     D     E       21/08/13 18:22:15
121  A      A      B      C     D     E       21/08/13 18:22:20
121  A      A      B      C     D     E1      21/08/13 18:22:25
121  A      A      B      C     D     E       21/08/13 18:22:30

Assume the first row is the INSERT and the rest are updates.

The only columns that have changed are
col2 & col6

So ideally i'd like to be able to have a query that shows me the data values and the columns that have changed since the initial insert.

So result would be (obviously we'd like to be able to identify the application user (this is not the Oracle user) and date/time of each update too though one step at a time...):

ID col3 col6 date_time

121 B     E     21/08/13 18:22:15
121 A     E     21/08/13 18:22:20
121 A     E1    21/08/13 18:22:25
121 A     E     21/08/13 18:22:30

Suggestions welcome!

-- 
jeremy

Received on Sun Aug 18 2013 - 13:20:29 CEST