Search code examples
oracle-databaseloggingrevision

Row-level revision control for Oracle?

I'm looking for an oracle package that will capture changes to a table and save them into a log or journal table. For example, executing a sql statements such as

insert into foo(x,y) values (12,34);

would capture these actions into an appropriate table, something like:

timestamp          who     operation   column    value
---------          ---     ---------   ------    -----
12-JAN-2012 13:22  MH      insert      x         12
12-JAN-2012 13:22  MH      insert      y         13

It would be great if there were facilities to generate sql statements based on this information

Solution

  • This can be done using fine grained auditing:

    http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG525

    As an alternative you can also turn on a flashback archive for the tables in question. Then you can query the content of the table at any point in the past. That won't show you who did the change though (and how it was done).