Oracle10g Row Timestamp
Oracle Database 10g provided a new pseudo-column, consisting of
the committed timestamp or SCN that provides applications and
users the ability to efficiently implement optimistic locking. In
previous releases, when posting updates to the database,
applications had to read in all column values or user-specified
indicator columns, compare them with those previously fetched, and
update those with identical values. With this feature, only the
row SCN needs to be retrieved and compared to verify that the row
has not changed from the time of the select to the update.
The pseudo-column for the committed SCN is called ora_rowscn
and is one of the version query pseudo-columns.
The ora_rowscn pseudo-column returns, for each version of each
row, the system change number (SCN) of the row. You cannot use
this pseudo-column in a query to a view.
However, you can use it to refer to the underlying table when
creating a view. You can also use this pseudo-column in the WHERE
clause of an UPDATE or DELETE statement.
Even though this pseudo-column is grouped with the restricted
version query pseudo-columns, this pseudo-column can be used like
any other pseudo-column. For example:
SQL> SELECT ora_rowscn FROM
used_boats:
ORA_ROWSCN
----------
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
13 rows selected.
The above query shows us that all of the records in used_boats
were committed in the same transaction. Let's update some of the
rows and see what happens.
SQL> UPDATE used_boats SET
price=price*1.1 WHERE seller_id=1;
3 rows updated.
SQL> commit;
Commit complete
SQL> SELECT ora_rowscn FROM used_boats:
ORA_ROWSCN
----------
816673
816673
816673
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
791744
13 rows selected.
Another convenient function allows you to retrieve the actual
time that the row was last altered through a conversion function
called scn_to_timestamp. Let's look at an example usage of this
function.
SQL> select
scn_to_timestamp(ora_rowscn) from used_boats;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
30-AUG-03 11.06.08.000000000 PM
30-AUG-03 11.06.08.000000000 PM
30-AUG-03 11.06.08.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
30-AUG-03 04.33.19.000000000 PM
13 rows selected.
The ora_rowscn has the following restrictions: This
pseudo-column is not supported for external tables or when
directly querying views.
The data from the SCN and timestamp pseudo-columns could prove
invaluable in a flashback situation. |
