How To Identify Time Of Last Row Update

BACKGROUND & OVERVIEW

Identifying the time a row was last updated used to involve either adding a column to the table itself and coding the application to update it with a date and time value, or switching on auditing with its performance overheads, or using Logminer to trawl through old archive logs.

Starting with Oracle 10g Release 1, you have the option to use the built-in ORA_ROWSCN function. It works very much like the version column technique described previously, but it can be performed automatically by Oracle¿you need no extra column in the table and no extra update/maintenance code to update this value.

ORA_ROWSCN is based on the internal Oracle system clock, the SCN. Every time you commit in Oracle, the SCN advances (other things can advance it as well, but it only advances; it never goes back). The concept is identical to the previous methods in that you retrieve ORA_ROWSCN upon data retrieval, and you verify it has not changed when you go to update. The only reason I give it more than passing mention is that unless you created the table to support the maintenance of ORA_ROWSCN at the row level, it is maintained at the block level. That is, by default many rows on a single block will share the same ORA_ROWSCN value. If you update a row on a block with 50 other rows, then they will all have their ORA_ROWSCN advanced as well. This would almost certainly lead to many false positives, whereby you believe a row was modified that in fact was not. Therefore, you need to be aware of this fact and understand how to change the behavior.

LISTING TIME OF LAST ROW UPDATE

SELECT
SCN_TO_TIMESTAMP ( ora_rowscn )
FROM
<table_name>
WHERE
<where_clause>
/

The values of ORA_ROWSCN available are constrained by the contents of SYS.SMON_SCN_TIME.

ERROR MESSAGES

ORA-08181

If the value supplied to the SCN_TO_TIMESTAMP function is either not a System Change Number or is considered by the database to be an SCN that is too old then an error message similar to the one in the following example will be returned:

select scn_to_timestamp( 14004 ) from dual;
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
ORA-06512: at line 1

DETERMINING THE OLDEST AVAILABLE SCN

In order to avoid an ORA-08181 error, it is possible to determine the oldest available SCN that the database can produce a timestamp from. The example below demonstrates retrieving that number, and the effects of attempting to use an SCN just one before it.

SQL> col min_scn for 99999999999999999
SQL> select min(SCN) min_scn from sys.smon_scn_time;

MIN_SCN
------------------
3330756740492

SQL> select SCN_TO_TIMESTAMP(3330756740492) from dual;

SCN_TO_TIMESTAMP(3330756740492)
---------------------------------------------------------------------------
09-OCT-10 08.40.33.000000000

SQL> select SCN_TO_TIMESTAMP(3330756740491) from dual;
select SCN_TO_TIMESTAMP(3330756740491) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line


Comment