ora_rowscn & scn_to_timestamp

最新推荐文章于 2021-04-13 16:43:31 发布

原创最新推荐文章于 2021-04-13 16:43:31 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

oracle和unix 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了Oracle数据库中ora_rowscn伪列的使用，展示如何通过SCN_TO_TIMESTAMP函数将系统变更号（SCN）转换为时间戳，以确定行的最后更新时间。创建带有ROWDEPENDENCIES的表可以获取更精确的行级更新时间，但会增加存储开销。此外，讨论了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.