| hai 的个人资料Oracle & Starcraft日志列表 |  工具  帮助 |
|
2月14日 ora_rowscn10g里面有了一项新功能，我们可以查看某个表的某一行最后一次改动的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.00000000 col 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.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的用途请各位自己去摸索吧。 |
|
|
