Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Dimitri.

Asked: July 13, 2020 - 1:12 pm UTC

Answered by: Connor McDonald - Last updated: July 14, 2020 - 6:54 am UTC

Category: SQL - Version: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.6.0.0.0

Viewed 100+ times

You Asked

Hello everyone,

I was excited about FlashBack Version Query feature and have a question

I want to track changes on some tables and it seamed that FlashBack Version Query had this feature.

For example, I could store last processed SCN, (select t.CURRENT_SCN from v$database t ) and make select like this:
select versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation,
       contract_id
  from noffer.contract_status_change versions BETWEEN SCN 12477243880505 and 12477244180201

we have archive logging for 3 hour interval. but I need track changes every second, maybe 4-5 times in second.

and it would filter changes between SCN. but it seems that it is not filtering, but giving all rows. Then I wrote "where clause" like this :
select versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation,
       contract_id
  from noffer.contract_status_change versions BETWEEN SCN 12477243880505 and 12477244180201
  where versions_startscn is not null or versions_endscn is not null

and received results I needed
but it's makes full scan and is very slow, makes full scan


is there some tricks to make this easily. I just need to know which rows (for this example contract_id changed- inserted, updated or deleted) for the given period (or for this example SCN). Some tables we have, there are 30-40 M per day

Or I need to write triggers for tracking data changes?

Thank you very much
Best
Dimitri Gamkrelidze

and we said...

Flashback Query *does* show changes, but be aware of what it is doing to *show* those changes. It is trawling through the undo segments for each row to undo any transactions that impacted that row.

That could be a lot of work. I would look at testing out flashback data archive (FDA), which persists the changes to disk thus reducing the workload, but similarly, its main intention is for "after the fact" analysis (like an audit trail), not necessarily being able to dive into that trail in near real time.

Also depending on your volume/requirements, it would be looking at change notification or query notification

eg https://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS018


If FDA or others above do not meet your needs, then you're potentially heading intro triggers