the best way to track data changes in oracle

Question

as the title i am talking about, what's the best way to track data changes in oracle? i just want to know which row being updated/deleted/inserted?

at first i think about the trigger, but i need to write more triggers on each table and then record down the rowid which effected into my change table, it's not good, then i search in Google, learn new concepts about materialized view log and change data capture,

materialized view log is good for me that i can compare it to original table then i can get the different records， even the different of the fields, i think the way is the same with i create/copy new table from original (but i don't know what's different?);

change data capture component is complicate for me :), so i don't want to waste my time to research it.

anybody has the experience the best way to track data changes in oracle?

Answer 1

You'll want to have a look at the AUDIT statement. It gathers all auditing records in the SYS.AUD$ table.

Example:

AUDIT insert, update, delete ON t BY ACCESS

Regards,
Rob.

Answer 2

You might want to take a look at Golden Gate. This makes capturing changes a snap, at a price but with good performance and quick setup.

If performance is no issue, triggers and audit could be a valid solution. If performance is an issue and Golden Gate is considered too expensive, you could also use Logminer or Change Data Capture. Given this choice, my preference would go for CDC. As you see, there are quite a few options, near realtime and offline.

Coding a solution by hand also has a price, Golden Gate is worth investigating.