Can I rely on the Oracle SCN to keep track of modified rows in my database? The goal is to query the database every x minutes for all the rows which have been modified in a particular table. The idea was to remember the current SCN using
SELECT current_scn FROM V$DATABASE;
And then after x minutes I would just run a query like
SELECT * from mytable where ORA_ROWSCN > current_scn_x_minutes_ago;
to get all the rows which have changed since then (and thus have a higher SCN). I am aware that the query will return too many results as the SCNs are managed per block but this does not matter. The main question I have is whether the SCN is strictly increasing, i.e. whether the next generated SCN is always higher than the current SCN (as queried from V$DATABASE) or whether I have to rely on SCN_TO_TIMESTAMP function to be sure that the ordering is correct.
The main question I have is whether the SCN is strictly increasing, i.e. whether the next generated SCN is always higher than the current SCN
According to the documentation: ORA_ROWSCN Pseudocolumn
Whether at the block level or at the row level, the ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row. However, with fine-grained ORA_ROWSCN, if two transactions T1 and T2 modified the same row R, one after another, and committed, a query on the ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2.
If a block is queried twice, then it is possible for the value of ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries. The only guarantee is that the value of ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments