the best way to track data changes in oracle

oracle triggers oracle9i materialized-views cdc
GBK picture GBK · Apr 20, 2011 · Viewed 17.2k times · Source

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

Rob van Wijk picture Rob van Wijk · Apr 20, 2011

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.

Related questions

PLSQL :NEW and :OLD

Can anyone help me understand when to use :NEW and :OLD in PLSQL blocks, I'm finding it very difficult to understand their usage.

Read More
ORA-04082: NEW or OLD references not allowed in table level triggers

itI have table named per. In the per table, I have a field named "fl1" and another field named "fl2". When updating a record, I want to check if the value of "fl1" has changed". If the value has changed, …

Read More
pragma autonomous_transaction in a trigger

I have written a trigger on one table which deletes data from other table upon a condition. The trigger has pragma autonomous_transaction, and trigger works as intended. However, I do wonder if there can be any problems in future, …

Read More