Re: Strange developer ideas

I've been asked similar things before - like comprehensive auditing of a legacy database with an eye to making a DW out of the results. I grabbed this from my GoldenGate notes in 2010:

1. Set the INSERTALLRECORDS Replication parameter to insert a new record in the target table for every change operation made to the source table. Beware this can eat up a lot of space, but if you need comprehensive auditing this is probably expected.
2. If you don't already have a CHANGED_BY_USERID and CHANGED_DATE attached to your records, you can use the Golden Gate functions on the target side to get this info for the current transaction. Check out the following functions in the GG Reference Guide:

_at_GETENV ('TRANSACTION','USERID' )

_at_GETENV ('TRANSACTION','TIMESTAMP' )

My usual caveat to the developers is to challenge them to make use of this collection of record change history. The apps are usually more 'chatty' than they expect with regards to data updates and transactions. How will they use this data to reconstruct change states of the data? There are no optimizations available like there may be with a properly designed data warehouse schema. The typical developer follow up is "Well if we were using MongoDB...." followed by a heavy DBA sigh.

-- 
Database Geekery at www.ba6.us / Twitter _at_ba6dotus

--
http://www.freelists.org/webpage/oracle-l