使用表的默认创建参数，即norowdependencies时，此时的ora_rawscn 取自data block header的SCN，此时对于同一个block里的row而言，ora_rowscn 是一样的。而在创建table时指定rowdependencies时，此时为每行row 保存一个ora_rowscn. 这样对于同一个block里的row，会有多个ora_rowscn 值。

下面进行测试：
1.默认情况下,即norowdependencies时:
SQL> create table t(id number,name varchar2(4000));

Table created.

SQL> insert into t select rownum,lpad(rownum,3000,'x') from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select id,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t order by id;

ID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------ ------------------------------------
1 4 518
2 4 518
3 4 519
4 4 519
5 4 520
6 4 520
7 4 516
8 4 516
9 4 517
10 4 517

10 rows selected.

SQL> select id,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24:mi:ss') from t order by id;

ID ORA_ROWSCN TO_CHAR(SCN_TO_TI
---------- ---------- -----------------
1 15054150 20140427 09:48:21
2 15054150 20140427 09:48:21
3 15054150 20140427 09:48:21
4 15054150 20140427 09:48:21
5 15054150 20140427 09:48:21
6 15054150 20140427 09:48:21
7 15054150 20140427 09:48:21
8 15054150 20140427 09:48:21
9 15054150 20140427 09:48:21
10 15054150 20140427 09:48:21

10 rows selected.

SQL> update t set name=lpad('10',3000,'x') where id=10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24:mi:ss') from t order by id;

ID ORA_ROWSCN TO_CHAR(SCN_TO_TI
---------- ---------- -----------------
1 15054150 20140427 09:48:21
2 15054150 20140427 09:48:21
3 15054150 20140427 09:48:21
4 15054150 20140427 09:48:21
5 15054150 20140427 09:48:21
6 15054150 20140427 09:48:21
7 15054150 20140427 09:48:21
8 15054150 20140427 09:48:21
9 15054433 20140427 09:53:22
10 15054433 20140427 09:53:22

10 rows selected.

可以看出，ora_rowscn是基于block的，id=10和id=9的记录被存放在block 517中，当修改了id=10的记录后，id=9的ora_rowscn也随之改变。

2. 创建表时指定了rowdependencies时

SQL> create table t2 (id number,name varchar2(4000)) rowdependencies;

Table created.

SQL> insert into t2 select rownum,lpad(rownum,3000,'x') from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select id,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t2 order by id;

ID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------ ------------------------------------
1 4 1582
2 4 1582
3 4 1583
4 4 1583
5 4 1584
6 4 1584
7 4 1580
8 4 1580
9 4 1581
10 4 1581

10 rows selected.

SQL> select id,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24:mi:ss') from t2 order by id;

ID ORA_ROWSCN TO_CHAR(SCN_TO_TI
---------- ---------- -----------------
1 15056359 20140427 10:05:45
2 15056359 20140427 10:05:45
3 15056359 20140427 10:05:45
4 15056359 20140427 10:05:45
5 15056359 20140427 10:05:45
6 15056359 20140427 10:05:45
7 15056359 20140427 10:05:45
8 15056359 20140427 10:05:45
9 15056359 20140427 10:05:45
10 15056359 20140427 10:05:45

10 rows selected.

SQL> update t2 set name=lpad('10',3000,'x') where id=10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyymmdd hh24:mi:ss') from t2 order by id;

ID ORA_ROWSCN TO_CHAR(SCN_TO_TI
---------- ---------- -----------------
1 15056359 20140427 10:05:45
2 15056359 20140427 10:05:45
3 15056359 20140427 10:05:45
4 15056359 20140427 10:05:45
5 15056359 20140427 10:05:45
6 15056359 20140427 10:05:45
7 15056359 20140427 10:05:45
8 15056359 20140427 10:05:45
9 15056359 20140427 10:05:45
10 15056407 20140427 10:06:41

10 rows selected.

这一次可见，只有id=10的记录的ora_rowscn发生了变化，而位于同一block上的id=9的记录未发生变化。

ITPub博客

ora_rowscn伪列测试

最新文章