Skip to Main Content
  • Questions
  • how to track modification of records on a table

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Shanmuga Priya.

Asked: October 17, 2016 - 8:00 am UTC

Last updated: May 11, 2020 - 5:18 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,
My existing functionality is having Triggers on about 15 tables for insert/update/delete. The modified rows are inserted into a target table.

However, I have been asked to use a different functionality (good performance) to track the records that are modified (insert/update/delete) on all these 15 tables.
Kindly help me on this.


Thank you,
Priya

and Chris said...

If you're on 11.2.0.4+ take a look at Flashback Data Archive. This tracks all changes to tables for you automatically!

To use it, just create an archive and add your tables to it:

create flashback archive app_archive 
  tablespace users retention 7 year;
 
alter table customer_addresses 
  flashback archive app_archive;


Storing data in the archive is handled by a background process. So the above fails, you won't see an error in your client:

https://blogs.oracle.com/sql/entry/why_you_can_get_ora

Keep an eye on the alert log!

You can then see changes as far back as the retention time using flashback query. For example:

/* What did the data look like yesterday? */
select * 
from   customer_addresses as of timestamp sysdate - 1;

/* What between yesterday and now? */
select * 
from   customer_addresses 
         versions between timestamp sysdate - 1 and sysdate;


If you're on 11.2.0.3 or lower this is a separately licenseable option. So you could pay for it... or just upgrade ;)

12c also includes an number of enhancements. This enables you to track more details about who changed rows, when, etc.:

https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

EJJI

Derzkii, September 19, 2017 - 7:18 am UTC

э алло! слышь TOM!!! КАК из dbf в oracle перевести! атветь
Chris Saxon
September 19, 2017 - 3:49 pm UTC

Errr... thanks? Sorry, but we can't read Russian(?) here...

Incremental Records

SS, May 11, 2020 - 12:46 am UTC

Hi,
How to handle delta/incremental records with a single SQL query to from a table ?
Connor McDonald
May 11, 2020 - 5:18 am UTC

Check out the VERSIONS BETWEEN syntax.

Its part of flashback - here's some more information



More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.