ORA_ROWSCN – When Was My Record Commited January 25, 2016
Posted by mwidlake in SQL.Tags: SQL
1 comment so far
I was going to do a follow-up post to my post on USERENV(‘COMMITSCN’) just to describe the slightly better known but still overlooked (and possibly more useful) ORA_ROWSCN – but I don’t need to!
As Neil Chandler has done this excellent post on it
Go and look at that post, it has all the information and detail you were actually looking for.
However, for any of you search-engine-seekers who can’t face the pain of following a link {it’s such a hard life for the modern knowledge-by-mouse-click generation}, here are the bare bones:
ORA_ROWSCN is a pseudo column that, by default, shows you the SCN (System Change Number) when the block was last changed. You can create a table with the ROWDEPENDENCIES extension to track it for individual rows but you can’t alter a table to add it. So usually you see a (conservative) last-change-scn for the block. See Neil’s blog post for more detail
To convert the SCN to a date/time there are various ways but Oracle will do it for you if it was within the last few days for you – with SCN_TO_TIMESTAMP function. If the row/block was last changed more than a few days ago, that function will error with ORA-08181 (I wish it just returned null rather than 08181, but then you can always wrap the call in your own function that handles that…)
Below is a short demo using test tables I don’t describe:
select house_number, addr_line_1,post_code ,ora_rowscn ,scn_to_timestamp(ora_rowscn) time_of_scn from address where house_number = 100 and addr_line_1 like 'COTHAM SOUTH%' / HOUSE NUMBER ADDR_LINE_1 POST_COD ORA_ROWSCN TIME_OF_SCN ------- -------------------- -------- ---------- ------------------------ 100 COTHAM SOUTH TERRACE SH5 8FA 11425626 24-JAN-16 20.44.56.00000 100 COTHAM SOUTH DRIVE LS20 1QY 11427281 24-JAN-16 20.51.29.00000 100 COTHAM SOUTH BD17 7JW 11437843 24-JAN-16 20.53.39.00000 100 COTHAM SOUTH TERRACE LS7 9SK 11448376 24-JAN-16 20.54.56.00000 100 COTHAM SOUTH TERRACE LS16 4SW 11460162 24-JAN-16 21.20.29.00000 100 COTHAM SOUTH TERRACE LS7 1GL 11461400 24-JAN-16 21.25.48.00000 100 COTHAM SOUTH LS20 1TO 11471921 24-JAN-16 21.26.53.00000 100 COTHAM SOUTH DRIVE LS1 5EJ 11471921 24-JAN-16 21.26.53.00000 100 COTHAM SOUTH DRIVE SG 3LO 11482461 24-JAN-16 21.28.05.00000 ... --However, if the change is more than about 120 hours ago... select surname,first_forename,dob,ora_rowscn ,scn_to_timestamp(ora_rowscn) time_of_scn from person where surname='KINOCK' and DOB between sysdate -10000 and sysdate -9500 / ,scn_to_timestamp(ora_rowscn) time_of_scn * ERROR at line 2: ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 -- which is a bit misleading as it was a valid SCN, just not for the SCN_TO_TIMESTAMP function -- remove the column based on scn_to_timestamp... select surname,first_forename,dob,ora_rowscn --,scn_to_timestamp(ora_rowscn) time_of_scn from person where surname='KINOCK' and DOB between sysdate -10000 and sysdate -9500 SURNAME FIRST_FORENAME DOB ORA_ROWSCN --------------- --------------- ----------------- ---------- KINOCK ABIGAIL 22-APR-1989 00:00 2518996 KINOCK FRANCESCA 23-APR-1989 00:00 2539749 KINOCK GIANO 10-NOV-1989 00:00 2567890 KINOCK GILLIAN 11-JAN-1990 00:00 2716278 ...
Look, you really should go and look at Neil’s post: click here