ora_rowscn & scn_to_timestamp

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

Syntax

Description of the illustration scn_to_timestamp.gif

Purpose

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.