Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【oracle】ORA_ROWSCN伪列

13 Aug 2019 » oracle

ORA_ROWSCN伪列

参考文档:https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm

英文文献如下:

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. 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 … [NOROWDEPENDENCIESROWDEPENDENCIES](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#CJAEEGDA) for more information on row-level dependency tracking.

You cannot use this pseudocolumn 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 pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query. Please refer to the SELECTflashback_query_clause for information on Flashback Query and “Version Query Pseudocolumns” for additional information on those pseudocolumns.

Restriction: This pseudocolumn is not supported for external tables.

Example The first statement below uses the ORA_ROWSCN pseudocolumn to get the system change number of the last operation on the employees table. The second statement uses the pseudocolumn with the SCN_TO_TIMESTAMP function to determine the timestamp of the operation:

ORA_ROWSCN可以获得最新修改行的数据。但是,不是最准确的。原因,参考上述信息。

ORA_ROWSCN不能用于视图,闪回查询,外部表。

例子如下:

上传cre_arp_monitor.sh ,并且部署用户。

SQL> select ORA_ROWSCN, t.user_name from t_portal_login t where t.user_id='153B42-2946';

ORA_ROWSCN
----------
USER_NAME
--------------------------------------------------------------------------------
1.0235E+13
zhangshengdong


SQL> select SCN_TO_TIMESTAMP(ORA_ROWSCN), t.user_name from t_portal_login t where t.user_id='153B42-2946';

SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
USER_NAME
--------------------------------------------------------------------------------
13-819 01.00.02.000000000 
zhangshengdong