Hi All,
I've searched for answers here but couldn't find anything that answers my specific questions.
So as other threads, I'm looking to use Flashback Data Archive on certain tables as a change capture service that shouldn't affect my user transactions. I'm looking to query the flashback tables with a watermark (SCN or timestamp) to search for changes and send these up to a cloud based system. I think I have the queries sorted out as the feature allows you to query between timestamps or SCNs, which is great. But, what I can't seem to find is any information on flashback archive lag against completed user transactions. I've assumed here that this isn't instantaneous as I haven't found any information that states this.
So if my changes pipeline were to query these tables to send them to the cloud, how do I find the latest SCN or timestamp to query?
Pseudo SQL would look like
SELECT "rows that have changed" FROM "current table" BETWEEN "lasttime I checked" AND "Last archive position of the archive process"
Then
UPDATE "watermarks table" SET watermark = "Last archive position of the archive process" WHERE tablename = "table" - so that I can shift the time window for each run.
As I'm using an ETL pipeline which loops through the tables in a dependency order I need to make sure that the point at which I sync up to is the same and up to date across the dependent tableset.
If there is lag, is there anyway to check this?
If there is, is there a better way to check what the last SCN is of the archive processor?
Another SQL vendor allows the following
SELECT sys.fn_cdc_get_max_lsn()
As far as I can tell
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
gets the current_timestamp of flashback in Oracle, but doesn't return anything different to CURRENT_TIMESTAMP
Any help would be appreciated thanks, if you know of any great books which explain this, I would appreciate the titles in a reply please.
Jim