14 May 2012

Find the last update date of a record

Since 10g, Oracle provides a new pseudocolumn ORA_ROWSCN, For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated.
SCN_TO_TIMESTAMP is a function used to convert the SCN to a readable date format.
SELECT empno, ename, ora_rowscn, scn_to_timestamp(ora_rowscn)
  FROM emp
 WHERE ename = 'SMITH' ;
 
EMPNO ENAME      ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
----- ---------- ---------- -----------------------------
 7369 SMITH       239054665 14/05/12 15:26:07,000000000
Note that when the SCN is too old, you could get the following error : ORA-08181: specified number is not a valid system change number

No comments:

Post a Comment