ORACLE 函数ORA_ROWSCN
ORA_ROWSCN Pseudocolumn
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 ... NOROWDEPENDENCIES | ROWDEPENDENCIES 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 SELECT ... flashback_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:
SELECT ORA_ROWSCN, last_name FROM employees WHERE employee_id = 188; SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees WHERE employee_id = 188一下是转帖（懒得自己写了）
今天看到一个新函数ora_rowscn(其实这个函数也不新了，从oracle10g R1 就有了，只是我不知道，嘿嘿。。。)，顺道在这里记录下。
ORA_ROWSCN是建立在oracle系统时钟（SCN）基础上，在表级应用的函数。
下例所示，在查询语句里直接调用ora_rowscn，就就能显示出每行最后一次的SCN，
Example1:
bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;
SID SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)
---------- ---------- ------------------------------------ ----------------------------------------
4574 5225 20552 10763660876119
4582 4371 20552 10763660876119
4583 30781 20552 10763660876119
4589 15658 20552 10763660876119
4551 2301 20554 10763660894774
4555 4724 20554 10763660894774
4563 7463 20554 10763660894774
4567 7283 20554 10763660894774
在上面的例子中，我分两批插入的数据，我们可以看到这两批数据的 scn好是不一样的。
在这里要特别注意的在默认状态下，oracle是在块级维护scn的。
Example2:
bu5705@ADW1U>update test1 set serial#=12345 where sid=4574;
1 row updated.
bu5705@ADW1U>commit;
Commit complete.
bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;
SID SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)
---------- ---------- ------------------------------------ ----------------------------------------
4574 12345 20552 10763669433340
4582 4371 20552 10763669433340
4583 30781 20552 10763669433340
4589 15658 20552 10763669433340
4551 2301 20554 10763660894774
4555 4724 20554 10763660894774
4563 7463 20554 10763660894774
4567 7283 20554 10763660894774
在Example2里我们可以清楚的看到我们只修改了sid=4574那一行的值，但是所有在block#为20552上所有数据的scn都因此而改变了。
假若想从行级跟踪SCN，就必须在create table 是定义rowdependencies.另外要注意的是这种改变
不能用简单的alter table语句来实现，必须重建table。
Example3:
bu5705@ADW1U>drop table test1;
Table dropped.
bu5705@ADW1U>create table test1 rowdependencies as select sid,serial# from v$session where rownum<5;
Table created.
bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;
SID SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)
---------- ---------- ------------------------------------ ----------------------------------------
4534 5955 20552 10763669640521
4540 1724 20552 10763669640521
4550 28663 20552 10763669640521
4561 28915 20552 10763669640521
bu5705@ADW1U>update test1 set serial#=12345 where sid=4534;
1 row updated.
bu5705@ADW1U>commit;
Commit complete.
bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;
SID SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)
---------- ---------- ------------------------------------ ----------------------------------------
4534 12345 20552 10763669664049
4540 1724 20552 10763669640521
4550 28663 20552 10763669640521
4561 28915 20552 10763669640521
最后还有一个小技巧，我们还可以通过使用scn_to_timestamp() 函数来转化scn，从而得知修改的时间
bu5705@ADW1U>select a.*, scn_to_timestamp(ora_rowscn) from test1 a;
SID SERIAL# SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------- ---------------------------------------------------------------------------
4574 5225 24-APR-12 03:58:33.000000000
4582 4371 24-APR-12 03:58:33.000000000
4583 30781 24-APR-12 03:58:33.000000000
4589 15658 24-APR-12 03:58:33.000000000
4551 2301 24-APR-12 03:59:27.000000000
4555 4724 24-APR-12 03:59:27.000000000
4563 7463 24-APR-12 03:59:27.000000000
4567 7283 24-APR-12 03:59:27.000000000
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/527318/viewspace-1073194/ ，如需转载，请注明出处，否则将追究法律责任。
