IT博客汇 | ora_rowscn小记

ora_rowscn小记

小荷发表于 2015-09-29 06:58:14

其实这个参数在10g就有了，之前一直没注意，发现比较有意思，这边记录一下。

ora_rowscn可以显示每一行的最近一次更改的scn，默认情况下，是以block为单位，如果一个block中有多行，那么这几行都是属于同一个scn。

create table时加ROWDEPENDENCIES，才是每行一个独立的scn。每行多需要6 bytes的大小。
另外，dump block的时候，时候看到dscn是0，这是因为rowscn是itl cleanout的时候，才会刷入到dscn中。

测试：

drop table t99;
create table t99 ROWDEPENDENCIES as select sysdate mydate from dual;

SQL> select to_char(SCN_TO_TIMESTAMP(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') as scn2time,
2 ora_rowscn,rowid,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 t99.* from test.t99 order by mydate;

SCN2TIME ORA_ROWSCN ROWID REL_FNO BLOCKNO ROWNO MYDATE
------------------- ---------- ------------------ ---------- ---------- ---------- ---------
2015-03-04 15:32:57 13838398 AAARTgAAEAAABdzAAA 4 6003 0 04-MAR-15

SQL>
SQL>
SQL>
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 6003;

System altered.

SQL> oradebug tracefile_name
/u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3173.trc
SQL> exit

cat /u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3173.trc
……
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00d3283e <<<<<already cleanout
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01001773
data_block_dump,data header at 0x7f8cf03c827c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x7f8cf03c827c
76543210
flag=--R-----
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f6f
avsp=0x1f5b
tosp=0x1f5b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f6f
block_row_dump:
tab 0, row 0, @0x1f6f
tl: 17 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.00d3283e <<<<<<<<<<<<<<<<<<<<<dscn，d3283e(十六进制)->13838398（十进制）
col 0: [ 7] 78 73 03 04 10 22 01
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 6003 maxblk 6003

继续测试：

insert into t99 select sysdate from dual;
exec dbms_lock.sleep(5);
insert into t99 select sysdate from dual;
commit;

SQL> select to_char(SCN_TO_TIMESTAMP(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') as scn2time,
2 ora_rowscn,rowid,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 t99.* from test.t99 order by mydate;

SCN2TIME ORA_ROWSCN ROWID REL_FNO BLOCKNO ROWNO MYDATE
------------------- ---------- ------------------ ---------- ---------- ---------- ---------
2015-03-04 15:32:57 13838398 AAARTgAAEAAABdzAAA 4 6003 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAA 4 6005 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAB 4 6005 1 04-MAR-15

SQL>
SQL> oradebug setmypid
Statement processed.
SQL>
SQL>
SQL> alter system dump datafile 4 block 6005;

System altered.

SQL> oradebug tracefile_name
/u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3224.trc
SQL> exit

cat /u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3224.trc
……
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01001775
data_block_dump,data header at 0x7f0634580a64
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x7f0634580a64
76543210
flag=--R-----
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 6005 maxblk 6005
……

继续测试：

insert into t99 select sysdate from dual;
exec dbms_lock.sleep(5);
commit;
exec dbms_lock.sleep(5);
insert into t99 select sysdate from dual;
commit;

SQL> set line 1000
SQL> select to_char(SCN_TO_TIMESTAMP(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') as scn2time,
2 ora_rowscn,rowid,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 t99.* from test.t99 order by mydate;

SCN2TIME ORA_ROWSCN ROWID REL_FNO BLOCKNO ROWNO MYDATE
------------------- ---------- ------------------ ---------- ---------- ---------- ---------
2015-03-04 15:32:57 13838398 AAARTgAAEAAABdzAAA 4 6003 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAA 4 6005 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAB 4 6005 1 04-MAR-15
2015-03-04 15:45:56 13839186 AAARTgAAEAAABd1AAC 4 6005 2 04-MAR-15
2015-03-04 15:46:02 13839192 AAARTgAAEAAABd1AAD 4 6005 3 04-MAR-15

SQL>
SQL>
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 6005
2 /

System altered.

SQL>
SQL>
SQL> oradebug tracefile_name
/u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3255.trc
SQL>

cat /u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3255.trc
……
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.00e.00000621 0x00c00566.1027.11 --U- 2 fsc 0x0000.00d3299e
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01001775
data_block_dump,data header at 0x7f14181f0a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f14181f0a64
76543210
flag=--R-----
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f60
tosp=0x1f60
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f87
0x14:pri[1] offs=0x1f76
block_row_dump:
tab 0, row 0, @0x1f87
tl: 17 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
col 0: [ 7] 78 73 03 04 10 27 25
tab 0, row 1, @0x1f76
tl: 17 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
col 0: [ 7] 78 73 03 04 10 27 2a
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 6005 maxblk 6005

继续测试，再来几个事务，造成delay block cleanout：

3个窗口分别：
SQL> insert into t99 select sysdate from dual;

1 row created.

SQL> rollback;

Rollback complete.

SQL>

SQL> set line 1000
SQL> select to_char(SCN_TO_TIMESTAMP(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') as scn2time,
2 ora_rowscn,rowid,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 t99.* from test.t99 order by mydate;

SCN2TIME ORA_ROWSCN ROWID REL_FNO BLOCKNO ROWNO MYDATE
------------------- ---------- ------------------ ---------- ---------- ---------- ---------
2015-03-04 15:32:57 13838398 AAARTgAAEAAABdzAAA 4 6003 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAA 4 6005 0 04-MAR-15
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAB 4 6005 1 04-MAR-15
2015-03-04 15:45:56 13839186 AAARTgAAEAAABd1AAC 4 6005 2 04-MAR-15
2015-03-04 15:46:02 13839192 AAARTgAAEAAABd1AAD 4 6005 3 04-MAR-15

SQL>
SQL>
SQL>
SQL> oradebug setmypid
Statement processed.
SQL>
SQL>
SQL>
SQL> alter system dump datafile 4 block 6005;

System altered.

SQL> oradebug tracefile_name
/u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3389.trc
SQL>

cat /u01/ora11g/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3389.trc
……
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.018.000004a6 0x00c0313d.0fe1.0b --U- 1 fsc 0x0000.00d32b58
0x02 0x0005.00c.00000657 0x00c00d83.0f71.03 C--- 0 scn 0x0000.00d32b52
bdba: 0x01001775
data_block_dump,data header at 0x7ffcfb71fa64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7ffcfb71fa64
76543210
flag=--R-----
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f54
avsp=0x1f3a
tosp=0x1f3a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f87
0x14:pri[1] offs=0x1f76
0x16:pri[2] offs=0x1f65
0x18:pri[3] offs=0x1f54
block_row_dump:
tab 0, row 0, @0x1f87
tl: 17 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.00d3299e <<<<<<<<<d3299e ->13838750 已经刷入block中
col 0: [ 7] 78 73 03 04 10 27 25
tab 0, row 1, @0x1f76
tl: 17 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.00d3299e <<<<<<<<<d3299e ->13838750 已经刷入block中
col 0: [ 7] 78 73 03 04 10 27 2a
tab 0, row 2, @0x1f65
tl: 17 fb: --H-FL-- lb: 0x0 cc: 1
dscn 0x0000.00d32b52 <<<<<d32b52 ->13839186 已经刷入block中
col 0: [ 7] 78 73 03 04 10 2e 38
tab 0, row 3, @0x1f54
tl: 17 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000 <<<<<<<0，还未刷入block中。重启下库，就刷进去了。
col 0: [ 7] 78 73 03 04 10 2f 06
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 6005 maxblk 6005

另外，再仔细看看insert 的sysdate，和rowid转成scn，再转成时间，其实还是有差距的。

SQL> select to_char(SCN_TO_TIMESTAMP(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') as scn2time,
2 ora_rowscn,rowid,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO,
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 t99.* from test.t99 order by mydate;

SCN2TIME ORA_ROWSCN ROWID REL_FNO BLOCKNO ROWNO MYDATE
------------------- ---------- ------------------ ---------- ---------- ---------- -------------------
2015-03-04 15:32:57 13838398 AAARTgAAEAAABdzAAA 4 6003 0 2015-03-04 15:33:00
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAA 4 6005 0 2015-03-04 15:38:36
2015-03-04 15:38:38 13838750 AAARTgAAEAAABd1AAB 4 6005 1 2015-03-04 15:38:41
2015-03-04 15:45:56 13839186 AAARTgAAEAAABd1AAC 4 6005 2 2015-03-04 15:45:55
2015-03-04 15:46:02 13839192 AAARTgAAEAAABd1AAD 4 6005 3 2015-03-04 15:46:05

SQL>

参考：AskTom – ORA_ROWSCN strange behavior