How to Find Out Last Modified Time of a Row in an Oracle 10g 11g Table Using ORA_ROWSCN and SCN_TO_TIMESTAMP Functions?

advertisements

_____________________________________________________________________________________________________________________

ora_rowscn pseudo column and SCN_TO_TIMESTAMP function

Oracle 11g database can link the system change number and corresponding timestamp for certain amount of time period. This short period of time depended on the auto-tuned undo retention period.
The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old. Ora_rowscn is the pseudo column linked with each record to track the changes to the row. SCN_TO_TIMESTAMP is a function to convert the scn number to corresponding timestamp.

See Example:

SQL> create table example as select * from all_objects where rownum<2;

Table created.

SQL> col ORA_ROWSCN for 99999999999999

  1* select object_name, ora_rowscn from example

SQL> /

OBJECT_NAME                         ORA_ROWSCN

------------------------------ ---------------

C_OBJ#                          11626841881118

SQL> select scn_to_timestamp(11626841881118) from dual;

SCN_TO_TIMESTAMP(11626841881118)

---------------------------------------------------------------------------

30-AUG-11 09.14.54.000000000 PM

Updating the Row.

SQL> update example set owner='xyz' ;

1 row updated.

SQL> commit;

Commit complete.

SQL> select object_name, ora_rowscn from example;

OBJECT_NAME                         ORA_ROWSCN

------------------------------ ---------------

C_OBJ#                          11626841881567

SQL> select scn_to_timestamp(11626841881567) from dual;

SCN_TO_TIMESTAMP(11626841881567)

---------------------------------------------------------------------------

30-AUG-11 09.22.53.000000000 PM

_____________________________________________________________________________________________________________________

Website Stats