ORACLE 函数ORA_ROWSCN

今天看到一个新函数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