ora_rowscn(zt)

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: 0x00411012 (1/69650)
scn: 0x0005.105a3ea8 seq: 0x02 flg: 0x02 tail: 0x3ea80602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x00411012
Object id on Block? Y
seg/obj: 0xff72 csc: 0x05.105a3bd0 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000d.026.000004da 0x01c01470.0184.2c --U- 1 fsc 0x0000.105a3be3
0x02 0x000e.008.000004e7 0x01c018a1.017d.17 --U- 1 fsc 0x0000.105a3ea8

data_block_dump,data header at 0xb72f845c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0xb72f845c
bdba: 0x00411012
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 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: 0x00411012 (1/69650)
scn: 0x0005.105a6cc1 seq: 0x02 flg: 0x02 tail: 0x6cc10602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
seg/obj: 0xff73 csc: 0x05.105a687c itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.005.000004e5 0x01c02e78.01c5.1e --U- 1 fsc 0x0000.105a6cb1
0x02 0x000c.02c.000004d4 0x01c00feb.01a5.24 --U- 1 fsc 0x0000.105a6cc1

data_block_dump,data header at 0xc70e45c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0c70e45c
bdba: 0x00411012
76543210
flag=--R-----
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f6c
tosp=0x1f6c
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f94
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000col 0: [ 2] c1 02
tab 0, row 1, @0x1f88
tl: 12 fb: --H-FL-- lb: 0x2 cc: 1
dscn 0x0000.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: 0x00411012 (1/69650)
scn: 0x0005.105a6dff seq: 0x02 flg: 0x02 tail: 0x6dff0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Object id on Block? Y
seg/obj: 0xff73 csc: 0x05.105a6dfd itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0012.024.000004db 0x01c024fd.016a.08 C--- 0 scn 0x0005.105a6df8
0x02 0x000d.00f.000004dc 0x01c031b3.0194.28 --U- 1 fsc 0x0000.105a6dff

data_block_dump,data header at 0xc70e45c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0c70e45c
bdba: 0x00411012
76543210
flag=--R-----
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f6c
tosp=0x1f6c
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f94
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f94
tl: 12 fb: --H-FL-- lb: 0x2 cc: 1
dscn 0x0005.105a6df8
col 0: [ 2] c1 02
tab 0, row 1, @0x1f88
tl: 12 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0005.105a6cc1col 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的用途請各位自己去摸索吧。