使用表的默认创建参数,即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的记录未发生变化。
下面进行测试:
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博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-1156546/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-1156546/