Oracle & Starcraft

10g里面有了一项新功能，我们可以查看某个表的某一行最后一次改动的scn，这个就是由ora_rowscn来提供。

SQL 10G>create table test (a number);
Table created.

SQL 10G>select ora_rowscn from test;
no rows selected

SQL 10G>insert into test values(1);
1 row created.

SQL 10G>column ora_rowscn format 999999999999999999999

insert 完成后我们可以查询到ora_rowscn

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
———————-
21749185488

SQL 10G>commit;
Commit complete.

当事务递交，ora_rowscn发生变化

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
———————-
21749185507

SQL 10G>insert into test values(2);
1 row created.

再插入一条记录，查看ora_rowscn，发现两条记录的ora_rowscn相同

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
———————-
21749185507
21749185507

SQL 10G>commit;
Commit complete.

事务递交后再次发现ora_rowscn发生变化

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
———————-
21749186216
21749186216

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
———————————— ————————————
1 69650
1 69650

SQL 10G>alter system dump datafile 1 block 69650;
System altered.

SQL 10G>exit

Start dump data blocks tsn: 0 file#: 1 minblk 69650 maxblk 69650
buffer tsn: 0 rdba: 0×00411012 (1/69650)
scn: 0×0005.105a3ea8 seq: 0×02 flg: 0×02 tail: 0×3ea80602
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0×00411012
Object id on Block? Y
seg/obj: 0xff72 csc: 0×05.105a3bd0 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0×0 ver: 0×01

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×000d.026.000004da 0×01c01470.0184.2c –U- 1 fsc 0×0000.105a3be3
0×02 0×000e.008.000004e7 0×01c018a1.017d.17 –U- 1 fsc 0×0000.105a3ea8

data_block_dump,data header at 0xb72f845c
===============
tsiz: 0×1fa0
hsiz: 0×16
pbl: 0xb72f845c
bdba: 0×00411012
76543210
flag=——–
ntab=1
nrow=2
frre=-1
fsbo=0×16
fseo=0×1f94
avsp=0×1f78
tosp=0×1f78
0xe:pti[0] nrow=2 offs=0
0×12:pri[0] offs=0×1f9a
0×14:pri[1] offs=0×1f94
block_row_dump:
tab 0, row 0, @0×1f9a
tl: 6 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0×1f94
tl: 6 fb: –H-FL– lb: 0×2 cc: 1
col 0: [ 2] c1 03
end_of_block_dump

SQL 10G>select to_number(’5105a3ea8′,’xxxxxxxxxxxxxxxxxx’) ora_rowscn from dual;
ORA_ROWSCN
——————
21749186216

从上面的实验中可以发现，当表以常规方式创建的时候，ora_rowscn取自data block header的scn，而每行数据并没保存自己的rowscn，下面来看一下以rowdependencies创建的表是什么情况。

SQL 10G>create table test (a number) rowdependencies;
Table created.

SQL 10G>select ora_rowscn from test;
no rows selected

SQL 10G>insert into test values(1);
1 row created.

insert后查询ora_rowscn ,发现ora_rowscn为空

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
——————

SQL 10G>commit;
Commit complete.

事务递交后，可以查询到ora_rowscn

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
——————
21749198001

SQL 10G>insert into test values(2);
1 row created.

插入第2条数据，依然发现有一个ora_rowscn为空

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
——————
21749198001

SQL 10G>commit;
Commit complete.

递交后查询ora_rowscn，发现2条记录的ora_rowscn并不一样，这和上面的情况不一样，我们知道这是因为ora_rowscn被保存在行内的缘故

SQL 10G>select ora_rowscn from test;
ORA_ROWSCN
——————
21749198001
21749198017

SQL 10G>alter system dump datafile 1 block 69650;
System altered.

SQL 10G>exit

Start dump data blocks tsn: 0 file#: 1 minblk 69650 maxblk 69650
buffer tsn: 0 rdba: 0×00411012 (1/69650)
scn: 0×0005.105a6cc1 seq: 0×02 flg: 0×02 tail: 0×6cc10602
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
seg/obj: 0xff73 csc: 0×05.105a687c itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0×0 ver: 0×01

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0010.005.000004e5 0×01c02e78.01c5.1e –U- 1 fsc 0×0000.105a6cb1
0×02 0×000c.02c.000004d4 0×01c00feb.01a5.24 –U- 1 fsc 0×0000.105a6cc1

data_block_dump,data header at 0xc70e45c
===============
tsiz: 0×1fa0
hsiz: 0×16
pbl: 0×0c70e45c
bdba: 0×00411012
76543210
flag=–R—–
ntab=1
nrow=2
frre=-1
fsbo=0×16
fseo=0×1f88
avsp=0×1f6c
tosp=0×1f6c
0xe:pti[0] nrow=2 offs=0
0×12:pri[0] offs=0×1f94
0×14:pri[1] offs=0×1f88
block_row_dump:
tab 0, row 0, @0×1f94
tl: 12 fb: –H-FL– lb: 0×1 cc: 1
dscn 0×0000.00000000
col 0: [ 2] c1 02
tab 0, row 1, @0×1f88
tl: 12 fb: –H-FL– lb: 0×2 cc: 1
dscn 0×0000.00000000
col 0: [ 2] c1 03
end_of_block_dump

dump block后发现，现在dscn还是0，我们查询出来的ora_rowscn实际上是从Scn/Fsc
中获得的，真正ora_rowscn被保存在行内是在itl发生cleanout时会把Scn/Fsc刷到dscn

SQL 10G>update test set a=1 where a=1;
1 row updated.

SQL 10G>commit;
Commit complete.

SQL 10G>update test set a=1 where a=1;
1 row updated.

SQL 10G>commit;
Commit complete.

SQL 10G>alter system dump datafile 1 block 69650;
System altered.

SQL 10G>exit

Start dump data blocks tsn: 0 file#: 1 minblk 69650 maxblk 69650
buffer tsn: 0 rdba: 0×00411012 (1/69650)
scn: 0×0005.105a6dff seq: 0×02 flg: 0×02 tail: 0×6dff0602
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Object id on Block? Y
seg/obj: 0xff73 csc: 0×05.105a6dfd itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0×0 ver: 0×01

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0012.024.000004db 0×01c024fd.016a.08 C— 0 scn 0×0005.105a6df8
0×02 0×000d.00f.000004dc 0×01c031b3.0194.28 –U- 1 fsc 0×0000.105a6dff

data_block_dump,data header at 0xc70e45c
===============
tsiz: 0×1fa0
hsiz: 0×16
pbl: 0×0c70e45c
bdba: 0×00411012
76543210
flag=–R—–
ntab=1
nrow=2
frre=-1
fsbo=0×16
fseo=0×1f88
avsp=0×1f6c
tosp=0×1f6c
0xe:pti[0] nrow=2 offs=0
0×12:pri[0] offs=0×1f94
0×14:pri[1] offs=0×1f88
block_row_dump:
tab 0, row 0, @0×1f94
tl: 12 fb: –H-FL– lb: 0×2 cc: 1
dscn 0×0005.105a6df8
col 0: [ 2] c1 02
tab 0, row 1, @0×1f88
tl: 12 fb: –H-FL– lb: 0×0 cc: 1
dscn 0×0005.105a6cc1
col 0: [ 2] c1 03
end_of_block_dump

另外，oracle还提供了ora_rowscn到timestamp的转换

SQL 10G>select scn_to_timestamp(ora_rowscn) from test;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
—————————————————————————
14-FEB-06 03.55.05.000000000 PM
14-FEB-06 03.44.05.000000000 PM

更多ora_rowscn的用途请各位自己去摸索吧。