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

Breadcrumb

May 4th

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.