jump to navigation

ORA_ROWSCN – When Was My Record Commited January 25, 2016

Posted by mwidlake in SQL.
Tags:
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