ora_rowscn & scn_to_timestamp

本文介绍了Oracle数据库中ora_rowscn伪列的使用,展示如何通过SCN_TO_TIMESTAMP函数将系统变更号(SCN)转换为时间戳,以确定行的最后更新时间。创建带有ROWDEPENDENCIES的表可以获取更精确的行级更新时间,但会增加存储开销。此外,讨论了SCN_TO_TIMESTAMP函数的精度限制和时效性问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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 scn_to_timestamp.gif follows
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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值