Tracking changes to tables in Oracle

On my story about using the minus SQL operator to compare tables, Blake asked what other more efficient methods could be used for tracking changes between tables.

Here are a couple options you could consider to compare table contents instead of the ‘minus’ operator:

Oracle change tables are probably the best for tracking changes internal to the database. You can see an example of them here.

This is what most Oracle data warehouse ETL is built with.

Custom triggers on insert, update and delete can enable similar functionality to change tables (change tables actually work with internal triggers.) You can have the triggers insert your data into another table or just keep some metadata in a separate table to indicate which rows have changed.

Simply adding an activity date column to your tables, populated with a trigger may be enough to track changes and keep databases in sync. If you know, for example, that the database was cloned at midnight on the 1st of the month, then to see what has changed since then you just need to examine all rows with an activity date after that. The activity date can be useful for several other things as well making this a fairly desirable approach.

Blake has an intriguingly eclectic blog himself which is well worth a visit. Thanks for the question Blake!

oracle, database, dba, database development, database administration

2 thoughts on “Tracking changes to tables in Oracle”

  1. Jon,

    I followed your link above and see where you mentioned CDC. CDC is really very easy to setup and once you do, you won’t go back to change tables and triggers. I know I won’t. I’ve written about Streams and CDC quite a bit on my blog.



  2. Jon, I have days, time, and seconds stored separately in GMT, datatype is number. I need to convert them to EST. I got the days converted, but how do I convert time and seconds?

    I am newbie to Oracle.


Leave a Reply

Your email address will not be published. Required fields are marked *