ora_rowscn

ora_rowscn是oracle10g引入的，在官方文档上有这段描述:

ORA_ROWSCN  reflects the system change-number (SCN) of the most recent change to a row. This change can be at the level of a block (coarse) or at the level of a row (fine-grained). The latter is provided by row-level dependency tracking. Refer to  CREATE   TABLE  ...  NOROWDEPENDENCIES | ROWDEPENDENCIES  for more information on row-level dependency tracking. In the absence of row-level dependencies,  ORA_ROWSCN  reflects block-level dependencies.

Whether at the block level or at the row level, the ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row.

ora_rowscn是伪列，所谓伪列就是不是真正存在的列，而是oracle为了维护工作而添加的内部字段。它可以用来记录行所在的块最后一次被修改的scn，注意，是提交之后的修改，如果所做的修改没有提交的话ora_rowscn是不会发生改变的。

ora_rowscn是数据块级别的，也就是同一个数据块中的行使用同一个ora_rowscn，而只要存储在这个块上的任何一行发生改变的话，这个块的ora_rowscn就会发生改变。

下面通过实验更深入的理解ora_rowscn的作用：

创建实验表：

HH@orcl 20-OCT-14>create table t1 (id int,name char(10)); 

Table created. 

HH@orcl 20-OCT-14>begin 
2 for i in 1..10000 
3 loop 
4 insert into t1 values(i,'oracle'); 
5 commit; 
6 end loop; 
7 end; 
8 / 

PL/SQL procedure successfully completed. 

HH@orcl 20-OCT-14>select count(*) from t1; 

COUNT(*) 
---------- 
10000

分别查询1，10, 100, 1000，10000的rowid,ora_rowscn，此时的scn是创建时刻的scn:

HH@orcl 20-OCT-14>select rowid,ora_rowscn,id,name from t1 where id in(1,10,100,1000,10000) order by id; 

ROWID ORA_ROWSCN ID NAME 
------------------ ---------- ---------- ---------- 
AAASj9AAHAAAACHAAA 2244307 1 oracle 
AAASj9AAHAAAACHAAJ 2244307 10 oracle 
AAASj9AAHAAAACHABj 2244307 100 oracle 
AAASj9AAHAAAACEAEM 2245223 1000 oracle 
AAASj9AAHAAAACaADB 2255550 10000 oracle

从ora_rowscn可以看出，id为1,10,100的记录是存放在同一个数据块上的，而id为1000和10000的记录也是分别存储在不同数据块上的。

下面分别查询id为1,10000的记录所在的数据块:

HH@orcl 20-OCT-14>select dbms_rowid.rowid_object('AAASj9AAHAAAACHAAA') data_object_id#,dbms_rowid.rowid_relative_fno('AAASj9AAHAAAACHAAA') rfile#, 
2 dbms_rowid.rowid_block_number('AAASj9AAHAAAACHAAA') block#, 
3 dbms_rowid.rowid_row_number('AAASj9AAHAAAACHAAA') row# 
4 from dual; 

DATA_OBJECT_ID# RFILE# BLOCK# ROW# 
--------------- ---------- ---------- ---------- 
76029                        7            135        0 

HH@orcl 20-OCT-14>select dbms_rowid.rowid_object('AAASj9AAHAAAACaADB') data_object_id#, 
2 dbms_rowid.rowid_relative_fno('AAASj9AAHAAAACaADB') rfile#, 
3 dbms_rowid.rowid_block_number('AAASj9AAHAAAACaADB') block#, 
4 dbms_rowid.rowid_row_number('AAASj9AAHAAAACaADB') row# 
5 from dual; 

DATA_OBJECT_ID# RFILE# BLOCK# ROW# 
--------------- ---------- ---------- ---------- 
76029                          7           154      193 

现在我们修改id为10000，1的记录并提交：

HH@orcl 20-OCT-14>update t1 set name='database' where id=10000; 

1 row updated. 

HH@orcl 20-OCT-14>commit; 

Commit complete.

HH@orcl 20-OCT-14>update t1 set name='sss' where id=1; 

1 row updated. 

HH@orcl 20-OCT-14>commit; 

Commit complete. 

再查询ora_rowscn:

HH@orcl 20-OCT-14>select rowid,ora_rowscn,id,name from t1 where id in(1,10,100,1000,10000) order by id; 

ROWID ORA_ROWSCN ID NAME 
------------------ ---------- ---------- ---------- 
AAASj9AAHAAAACHAAA 2256414 1 sss 
AAASj9AAHAAAACHAAJ 2256414 10 oracle 
AAASj9AAHAAAACHABj 2256414 100 oracle 
AAASj9AAHAAAACEAEM 2245223 1000 oracle 
AAASj9AAHAAAACaADB 2256367 10000 database 

HH@orcl 20-OCT-14>select dbms_rowid.rowid_object('AAASj9AAHAAAACHAAA') data_object_id#,dbms_rowid.rowid_relative_fno('AAASj9AAHAAAACHAAA') rfile#, 
2 dbms_rowid.rowid_block_number('AAASj9AAHAAAACHAAA') block#, 
3 dbms_rowid.rowid_row_number('AAASj9AAHAAAACHAAA') row# 
4 from dual; 

DATA_OBJECT_ID# RFILE# BLOCK# ROW# 
--------------- ---------- ---------- ---------- 
76029                        7          135          0 

HH@orcl 20-OCT-14>select dbms_rowid.rowid_object('AAASj9AAHAAAACaADB') data_object_id#, 
2 dbms_rowid.rowid_relative_fno('AAASj9AAHAAAACaADB') rfile#, 
3 dbms_rowid.rowid_block_number('AAASj9AAHAAAACaADB') block#, 
4 dbms_rowid.rowid_row_number('AAASj9AAHAAAACaADB') row# 
5 from dual; 

DATA_OBJECT_ID# RFILE# BLOCK# ROW# 
--------------- ---------- ---------- ---------- 
76029                        7             154        193 

可以看到，我们只是更新了135号块中id为1的记录，但是135号块中id为10,100的记录的ora_rowscn也都改变了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29800581/viewspace-1304998/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29800581/viewspace-1304998/