SQL> select ora_rowscn from tt;
ORA_ROWSCN
----------
286928
286928
286928
286928
286928
286928
286928
286928
286928
286928
10 rows selected.
SQL> SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) from tt;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
03-MAY-13 04.07.33.000000000 PM
this pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Please refer to CREATE
TABLE
... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.
SQL> create table tt_dep ROWDEPENDENCIES as select * from tt;
Table created.
SQL> desc tt_dep;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(1)
B VARCHAR2(50)
C NUMBER
D NUMBER
E NUMBER
F VARCHAR2(50)
rowdependencies will take 6bytes each row to store the timestamp of the row. for me I think it's another way to track the row's updating
ROWDEPENDENCIES Specify ROWDEPENDENCIES
if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.
NOROWDEPENDENCIES Specify NOROWDEPENDENCIES
if you do not want table
to use the row-level dependency tracking feature. This is the default.
SCN_TO_TIMESTAMP
Description of the illustration scn_to_timestamp.gif
SCN_TO_TIMESTAMP
takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP
datatype. This function is useful any time you want to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN
pseudocolumn to associate a timestamp with the most recent change to a row.
Notes:
-
The usual precision of the result value is 3 seconds.
-
The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. 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.