Setup a new table and insert rows.
create table blockdetail ( id number, descr varchar(20));
Table created.
insert into blockdetail values (1, 'First row');
1 row created.
insert into blockdetail values (2, 'Second row');
1 row created.
insert into blockdetail values (3, 'Third row');
1 row created.
insert into blockdetail values (4, 'Forth row');
1 row created.
insert into blockdetail values (5, 'Fith row');
1 row created.
insert into blockdetail values (6, 'Sixth row');
1 row created.
insert into blockdetail values (7, 'Seventh row');
1 row created.
insert into blockdetail values (8, 'Eighth row');
1 row created.
commit;
Commit complete.
alter system flush buffer_cache;
System altered.
Check which datafile/block holds the table and that all rows are in the same block.
select rowid,id,
dbms_rowid.rowid_relative_fno(rowid) fileno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno,
rownum
from blockdetail;
ROWID ID FILENO BLOCKNO ROWNO ROWNUM
------------------ ---------- ---------- ---------- ---------- ----------
AAAuxzAAGAAAd41AAA 1 6 122421 0 1
AAAuxzAAGAAAd41AAB 2 6 122421 1 2
AAAuxzAAGAAAd41AAC 3 6 122421 2 3
AAAuxzAAGAAAd41AAD 4 6 122421 3 4
AAAuxzAAGAAAd41AAE 5 6 122421 4 5
AAAuxzAAGAAAd41AAF 6 6 122421 5 6
AAAuxzAAGAAAd41AAG 7 6 122421 6 7
AAAuxzAAGAAAd41AAH 8 6 122421 7 8
8 rows selected.
alter system dump datafile 6 block 122421;
System altered.
Having a look at the trace file i can see the following
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc695ba itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.0000926b 0x00c02e54.24b0.3a --U- 8 fsc 0x0000.0cc696ac
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0181de35
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 9] 46 69 72 73 74 20 72 6f 77
tab 0, row 1, @0x1f77
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [10] 53 65 63 6f 6e 64 20 72 6f 77
tab 0, row 2, @0x1f67
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 9] 54 68 69 72 64 20 72 6f 77
tab 0, row 3, @0x1f57
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05
col 1: [ 9] 46 6f 72 74 68 20 72 6f 77
tab 0, row 4, @0x1f48
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06
col 1: [ 8] 46 69 74 68 20 72 6f 77
tab 0, row 5, @0x1f38
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 07
col 1: [ 9] 53 69 78 74 68 20 72 6f 77
tab 0, row 6, @0x1f26
tl: 18 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 08
col 1: [11] 53 65 76 65 6e 74 68 20 72 6f 77
tab 0, row 7, @0x1f15
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 09
col 1: [10] 45 69 67 68 74 68 20 72 6f 77
end_of_block_dump
From the dump above the header shows 1 ITL showing 8 commited rows, and the
block row dump shows my 8 rows.
Updating the the table to modify 1 row and looking at the changes.
update blockdetail set descr = 'Modify row' where id = 1;
1 row updated.
alter system flush buffer_cache;
System altered.
alter system dump datafile 6 block 122421;
System altered.
Now when I dump the block i notice the Block dump from cache
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=25288245
BH (0x8afe2c48) file#: 6 rdba: 0x0181de35 (6/122421) class: 1 ba: 0x8ad24000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 191603 objn: 191603 tsn: 4 afn: 6 hint: f
hash: [0x80fbfda8,0xddbfe718] lru: [0x78fb3400,0x77f7e1c0]
obj-flags: object_ckpt_list
ckptq:[0x878,0x74f8] fileq:[0xddf8,0xddf8] objq:[0xd7628,0xd7a28]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0x1fb6.14daa.0] LSCN: [0x0.cc699be] HSCN: [0x0.cc699be] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
The cached block status = XCURRENT
Further looking at the block header dump and block row dump sections
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc695ba itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.0000926b 0x00c02e54.24b0.3a --U- 8 fsc 0x0000.0cc696ac
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0181de35
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 9] 46 69 72 73 74 20 72 6f 77
The Block header and block row dump section still show no changes.
Therefor the block was not actually flushed to disk when we flushed the buffer cache.
Lets try flushing the buffer cache again.
alter system flush buffer_cache;
System altered.
alter system dump datafile 6 block 122421;
System altered.
Below I can see that the Block dump from cache section shows the cached block as free (st: FREE).
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=25288245
BH (0x8afe2c48) file#: 6 rdba: 0x0181de35 (6/122421) class: 1 ba: 0x8ad24000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 191603 objn: 191603 tsn: 4 afn: 6 hint: f
hash: [0x80fbfda8,0xddbfe718] lru: [0x72fde850,0x80fca1c0]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
So the DML changes should show on the disk block. In the Block header dump we see Itl 0x02 has 1 uncommited row. The block row dump section shows the new uncommited hex value for col 1:
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc69995 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.0000926b 0x00c02e54.24b0.3a C--- 0 scn 0x0000.0cc696ac
0x02 0x0014.000.00002b19 0x00c03493.15db.0f ---- 1 fsc 0x0000.00000000
bdba: 0x0181de35
block_row_dump:
tab 0, row 0, @0x1f04
tl: 17 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [10] 4d 6f 64 69 66 79 20 72 6f 77
Now update another 2 rows, and flush to disk
commit;
Commit complete.
update blockdetail set descr = 'Modifed two rows' where id in (2,3);
2 rows updated.
alter system flush buffer_cache;
System altered.
alter system dump datafile 6 block 122421;
System altered.
Now below we can see the 2 uncommited rows in the block header (ITL 0x01).
The block row dump section shows the uncommited changes for row 1 and 2.
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc69fe2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0016.01f.00002b00 0x00c04331.17b8.2f ---- 2 fsc 0x0000.00000000
0x02 0x0014.000.00002b19 0x00c03493.15db.0f C--- 0 scn 0x0000.0cc69c3e
block_row_dump:
tab 0, row 0, @0x1f04
tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 4d 6f 64 69 66 79 20 72 6f 77
tab 0, row 1, @0x1eed
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [16] 4d 6f 64 69 66 65 64 20 74 77 6f 20 72 6f 77 73
tab 0, row 2, @0x1ed6
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [16] 4d 6f 64 69 66 65 64 20 74 77 6f 20 72 6f 77 73
Lets delete a row from the table and see the changes to the block
commit;
Commit complete.
delete from blockdetail where id = 4;
1 row deleted.
alter system flush buffer_cache;
System altered.
alter system dump datafile 6 block 122421;
System altered.
Below in the block row dump section, tab 0, row 3 has been deleted (--HDFL--)
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc6b8f4 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0016.01f.00002b00 0x00c04331.17b8.2f C--- 0 scn 0x0000.0cc6b8b8
0x02 0x000c.00d.00003dc6 0x00c04740.1843.1c ---- 1 fsc 0x000e.00000000
block_row_dump:
tab 0, row 0, @0x1f04
tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 4d 6f 64 69 66 79 20 72 6f 77
tab 0, row 1, @0x1eed
tl: 23 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [16] 4d 6f 64 69 66 65 64 20 74 77 6f 20 72 6f 77 73
tab 0, row 2, @0x1ed6
tl: 23 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [16] 4d 6f 64 69 66 65 64 20 74 77 6f 20 72 6f 77 73
tab 0, row 3, @0x1f57
tl: 2 fb: --HDFL-- lb: 0x2
Lets look at Oracle ASSM increase the transaction slots.
I have not commited the delete above, and in another 3 sessions update 1 row each.
Finaly we flush the buffer cache and dump the block again.
We can see in the Block Header dump the additional 2 transaction slots (0x03, 0x04).
Block header dump: 0x0181de35
Object id on Block? Y
seg/obj: 0x2ec73 csc: 0x00.cc6be4c itc: 4 flg: E typ: 1 - DATA
brn: 0 bdba: 0x181de30 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.00e.00001ea8 0x00c00d66.12fd.30 ---- 1 fsc 0x0006.00000000
0x02 0x000c.00d.00003dc6 0x00c04740.1843.1c ---- 1 fsc 0x000e.00000000
0x03 0x0007.015.00008072 0x00c061b9.23c5.2d ---- 1 fsc 0x000c.00000000
0x04 0x002b.012.00001c41 0x00c003f6.125b.1f ---- 1 fsc 0x0005.00000000
******************************************
keywords: dump
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment