ORA_ROWSCN用于返回相关行记录的SCN
SQL> create table test as select object_id,rpad(object_name,2000,' ') object_name from user_objects;
表已创建。
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
2510609 147
2510609 147
2510609 147
2510609 148
2510609 148
2510609 148
2510609 149
2510609 149
2510609 149
2510609 150
SQL> update test set object_name='TestForScn' where object_id=73198;
已更新 1 行。
SQL> select ora_rowscn,object_id from test where rownum<11;
ORA_ROWSCN OBJECT_ID
---------- ----------
2510609 73198
2510609 73199
2510609 74645
2510609 74646
2510609 74647
2510609 74648
2510609 74649
2510609 74650
2510609 75364
2510609 75328
已选择10行。
SQL> commit;
提交完成。
SQL> select ora_rowscn,object_id,dbms_rowid.rowid_block_number(rowid) block_id from test where rownum<11;
ORA_ROWSCN OBJECT_ID BLOCK_ID
---------- ---------- ----------
2510800 73198 147
2510800 73199 147
2510800 74645 147
2510609 74646 148
2510609 74647 148
2510609 74648 148
2510609 74649 149
2510609 74650 149
2510609 75364 149
2510609 75328 150
已选择10行。
SCN通常在commit 时增进,虽然更新了一条数据,但是由于被更新的记录和其他2条都位于147号block上,导致3条SCN被改变,并且这3条记录的ORA_ROWSCN都是从数据块头文件中获取的。
SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='TEST' and owner='SCOTT';
SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS
-------------------- ---------- ---------- ----------
TEST 4 144 8
TEST 4 152 8
SQL> alter system dump datafile 4 block 147;
SQL> select to_number('264fd0','xxxxxx') from dual;
TO_NUMBER('264FD0','XXXXXX')
----------------------------
2510800
SQL> select to_number('264f11','xxxxxx') from dual;
TO_NUMBER('264F11','XXXXXX')
----------------------------
2510609
如果建表时启动了ROWDEPENDENCIES:
SQL> create table test1 rowdependencies as select * from test;
表已创建。
SQL> select ora_rowscn,dbms_rowid.rowid_block_number(rowid),object_id from test1 where rownum<11;
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) OBJECT_ID
---------- ------------------------------------ ----------
2512020 691 73198
2512020 691 73199
2512020 691 74645
2512020 691 74646
2512020 692 74647
2512020 692 74648
2512020 692 74649
2512020 693 74650
2512020 693 75364
2512020 693 75328
已选择10行。
SQL> update test1 set object_name='TestForDependencies' where object_id=73198;
已更新 1 行。
SQL> select ora_rowscn,dbms_rowid.rowid_block_number(rowid),object_id from test1 where rownum<11;
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) OBJECT_ID
---------- ------------------------------------ ----------
691 73198
2512020 691 73199
2512020 691 74645
2512020 691 74646
2512020 692 74647
2512020 692 74648
2512020 692 74649
2512020 693 74650
2512020 693 75364
2512020 693 75328
已选择10行。
SQL> commit;
提交完成。
SQL> select ora_rowscn,dbms_rowid.rowid_block_number(rowid),object_id from test1 where rownum<11;
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) OBJECT_ID
---------- ------------------------------------ ----------
2512085 691 73198
2512020 691 73199
2512020 691 74645
2512020 691 74646