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 switiching on auditing with its performance overheads, or using Log Miner to trawl through old archive logs.

Since Oracle 10g, a new pseudo-column - ORA_ROWSCN - has been introduced which makes life simpler.

Note: This method only gives an approxination of the time of update as it is limited to the time deviation of the SCN to which the update belongs.

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-00904

The pseudo column ORA_ROWSCN only exists for tables. If an attempt is made to query the column against a view, the following error message will be returned:

ERROR at line 1:

ORA-00904: "ORA_ROWSCN": invalid identifier

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;

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 demonstates retreiving 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
© copyright 2001-2014 ABCdba.com | all rights reserved