ITPub博客

首页 > 数据库 > Oracle > ORACLE 函数ORA_ROWSCN

ORACLE 函数ORA_ROWSCN

Oracle 作者:wtjiang2008 时间:2014-01-20 17:20:57 0 删除 编辑
在做实验,遇到这个函数不知道啥意思,就记录一下。

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博客 ” ,链接:http://blog.itpub.net/527318/viewspace-1073194/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-30

  • 博文量
    30
  • 访问量
    191704