oracle block other,oracle block数据块结构续(一)-CSDN博客

/**********查看254条记录以上的数据块**********/

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from t_block group by dbms_rowid.rowid_block_number(rowid) having count(*)>250;

DBMS_ROWID.ROWID_BLOCK_NUMBER( COUNT(*)

------------------------------ ----------

50398 484

/***********dump上述一个数据块**************/

SQL> alter system dump datafile 5 block 50535;

System altered

/**********定位trc文件******************/

SQL> select * from v$diag_info;

INST_ID NAME VALUE

---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------

1 Default Trace File d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_17324.trc

11 rows selected

/***********未删除此数据块前的dump*************/

Start dump data blocks tsn: 5 file#:5 minblk 50535 maxblk 50535

Block dump from cache:

Dump of buffer cache at level 4 for tsn=5, rdba=21022055

BH (0x000007FF41FBC528) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF41918000

set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28

dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f

hash: [0x000007FF607493B8,0x000007FF607493B8] lru: [0x000007FF41FBC740,0x000007FF41FBC4E0]

obj-flags: object_ckpt_list

ckptq: [0x000007FF41FBC2C8,0x000007FF41FBC788] fileq: [0x000007FF41FBC2D8,0x000007FF41FBC798] objq: [0x000007FF41FBC768,0x000007FF41FBC508]

st: XCURRENT md: NULL tch: 3

flags: buffer_dirty redo_since_read

LRBA: [0xc2.283c.0] LSCN: [0x0.5b1fe9] HSCN: [0x0.5b1ff8] HSUB: [1]

cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 5 rdba: 0x0140c567 (5/50535)

scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001

frmt: 0x02 chkval: 0x6327 type: 0x00=unknown

Hex dump of block: st=0, typ_found=0

Dump of memory from 0x000000001B978200 to 0x000000001B97A200

01B978200 0000A200 0140C567 00000000 05010000 [....g.@.........]

01B978210 00006327 00000000 00000000 00000000 ['c..............]

01B978220 00000000 00000000 00000000 00000000 [................]

Repeat 508 times

01B97A1F0 00000000 00000000 00000000 00000001 [................]

Dump of memory from 0x000000001B978214 to 0x000000001B97A1FC

01B978210 00000000 00000000 00000000 [............]

01B978220 00000000 00000000 00000000 00000000 [................]

Repeat 508 times

01B97A1F0 00000000 00000000 00000000 [............]

End dump data blocks tsn: 5 file#: 5 minblk 50535 maxblk 50535

SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=50535;

484 rows deleted

SQL> alter system dump datafile 5 block 50535;

System altered

/***********删除数据块后的dump********/

Start dump data blocks tsn: 5 file#:5 minblk 50535 maxblk 50535

Block dump from cache:

Dump of buffer cache at level 4 for tsn=5, rdba=21022055

BH (0x000007FF41FA4208) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF4168C000

set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28

dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f

hash: [0x000007FF41FBC5D8,0x000007FF607493B8] lru: [0x000007FF43F98620,0x000007FF41FA41C0]

obj-flags: object_ckpt_list

ckptq: [0x000007FF41FBC2C8,0x000007FF41FBC788] fileq: [0x000007FF41FBC2D8,0x000007FF41FBC798] objq: [0x000007FF41FBC768,0x000007FF41FBC508]

st: XCURRENT md: NULL tch: 2

flags: buffer_dirty redo_since_read

LRBA: [0xc2.283c.0] LSCN: [0x0.5b1fe9] HSCN: [0x0.5b2176] HSUB: [1]

cr pin refcnt: 0 sh pin refcnt: 0

BH (0x000007FF41FBC528) file#: 5 rdba: 0x0140c567 (5/50535) class: 1 ba: 0x000007FF41918000

set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 979,28

dbwrid: 0 obj: 72971 objn: 72971 tsn: 5 afn: 5 hint: f

hash: [0x000007FF607493B8,0x000007FF41FA42B8] lru: [0x000007FF607C46F0,0x000007FF43F7BF50]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL] objq: [NULL]

st: CR md: NULL tch: 2

cr: [scn: 0x0.5b2174],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.5b2174],[sfl: 0x0],[lc: 0x0.5b1ff8]

flags: redo_since_read

cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 5 rdba: 0x0140c567 (5/50535)

scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 ----发现即便修改了数据块，数据块的scn没有变化

frmt: 0x02 chkval: 0x6327 type: 0x00=unknown

Hex dump of block: st=0, typ_found=0

Dump of memory from 0x000000001B978200 to 0x000000001B97A200

01B978200 0000A200 0140C567 00000000 05010000 [....g.@.........]

01B978210 00006327 00000000 00000000 00000000 ['c..............]

01B978220 00000000 00000000 00000000 00000000 [................]

Repeat 508 times

01B97A1F0 00000000 00000000 00000000 00000001 [................]

Dump of memory from 0x000000001B978214 to 0x000000001B97A1FC

01B978210 00000000 00000000 00000000 [............]

01B978220 00000000 00000000 00000000 00000000 [................]

Repeat 508 times

01B97A1F0 00000000 00000000 00000000 [............]

End dump data blocks tsn: 5 file#: 5 minblk 50535 maxblk 50535

SQL> --提交commit看下block scn是否发生变化

SQL> commit;

Commit complete

SQL> alter system dump datafile 5 block 50535;

System altered

buffer tsn: 5 rdba: 0x0140c567 (5/50535)

scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001 --提交commit后的scn没有发生变化

SQL> --发生检查点我们看下是否发生变化

SQL> alter system checkpoint;

System altered

SQL> alter system dump datafile 5 block 50535;

System altered

scn: 0x0000.005b2211 seq: 0x01 flg: 0x06 tail: 0x22110601 --发生检查点后数据块的scn确实发生了变化

小结：1,block scn是指发生检查点后已经写入到数据文件中的scn

2,如果未发生检查点，block scn一直是不变化的

/*********测试下对同一个数据块变化多次scn及seq如何变化的，试着找到一点儿知识***********/

SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=49475 and rownum=1;--仅删除数据块的一条记录

1 row deleted

SQL> commit;

Commit complete

SQL> alter system dump datafile 5 block 49475;

System altered

SQL> select * from v$diag_info;

INST_ID NAME VALUE

---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------

1 Default Trace File d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_17324.trc

11 rows selected

/********未更新前的trace dump*********/

scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601

scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601 --更新某个数据块300条记录后block scn没有变化

小结：1,oracle11g r2上，对于某个数据块不发生检查点，更新这数据块上面的所有表记录，block scn不会发生变化

2,seq我没发现有什么变化，一直是01

scn: 0x0000.005b1ff8 seq: 0x01 flg: 0x06 tail: 0x1ff80601 --检查点前的某块scn

/****************作操作但不提交commit*****************/

SQL> delete from t_block where dbms_rowid.rowid_block_number(rowid)=50300 and rownum=1;

1 row deleted

/****************发生检查点*************************/