the statement is quite easy:
update berx.TEST_BLOBS set (comments, file_content) = (select comments, file_content from berx.source_blobs where id=4) ;
The new CLOB is about 11.1 kb in size - so it will require more than 1 block.
Fortunately the number of extents did not change.
New information needs to be gathered again:
ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_PRE_BLOB"; alter system dump datafile 4 block min 255360 block max 255375; ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_PRE_EXT2"; alter system dump datafile 4 block min 256512 block max 257023;
@LOBMAP.sql (row 1) System altered. SQL> ROWID = AABu7gAAEAAA+N7AAA ROWNUM = 1 LOBID = 00000001000099BF531D EXTENT# = 0 HOLE? = n Superchunk cont? = n Overallocation = n rdba = 17032588 - 0x 103E58C File = 4 Block = 255372 nblks = 2 offset = 0 length = 11389 ---
The LOBID changed. So the lob (for the same row) is in another location now.
In the 2nd extent, as far as I can judge, did not change.
In the 1st extend, some things changed (obviously, somewhere the LOB needs to be stored).
NGLOB: Segment Header stays the same.
all the NGLOB: Hash Bucket blocks stays the same. (that confuses me as I expected at least one of them to change)
Also block 0x0103e58b (trans data) - the one which holds the previous LOB is exactly the same (!)
The new trans data blocks
1st block:
buffer tsn: 4 rdba: 0x0103e58c (4/255372)
scn: 0xa9c.9062fe3c seq: 0x02 flg: 0x04 tail: 0xfe3c0602
frmt: 0x02 chkval: 0xba2b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F98ED985E00 to 0x00007F98ED987E00
7F98ED985E00 0000A206 0103E58C 9062FE3C 04020A9C [........<.b.....]
7F98ED985E10 0000BA2B 00000005 0006EF63 9062FE3C [+.......c...<.b.]
7F98ED985E20 00000A9C 00220001 FFFFFFFF 001901B0 [......".........]
7F98ED985E30 00020F33 00000000 00000000 00004000 [3............@..]
7F98ED985E40 00000000 00000000 00000000 9062FE3C [............<.b.]
7F98ED985E50 00200A9C 01000000 BF990000 00001D53 [.. .........S...]
7F98ED985E60 00000000 00000000 00000000 00000000 [................]
7F98ED985E70 00000000 00000000 00000000 00001F7C [............|...]
7F98ED985E80 70747468 2F2F3A73 616E6F6A 6E616874 [https://jonathan]
7F98ED985E90 6977656C 6F772E73 72706472 2E737365 [lewis.wordpress.]
7F98ED985EA0 2F6D6F63 35313032 2F31302F 6C2F3132 [com/2015/01/21/l]
7F98ED985EB0 732D626F 65636170 0D0A0D2F 6572630A [ob-space/....cre]
...
Block header dump: 0x0103e58c
Object id on Block? Y
seg/obj: 0x6ef63 csc: 0xa9c.9062fe3c itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x01b0.019.00020f33 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x0103e58c]
kdlich [0x7f98ed985e4c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0a9c.9062fe3c
lid 00000001000099bf531d
rid 0x00000000.0000
kdlidh [0x7f98ed985e64 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 8060
spr 0
data [0x7f98ed985e80 52 8060]
68 74 74 70 73 3a 2f 2f 6a 6f 6e 61 74 68 61 6e 6c 65 77 69 73 2e 77 6f 72 64
70 72 65 73 73 2e 63 6f 6d 2f 32 30 31 35 2f 30 31 2f 32 31 2f 6c 6f 62 2d 73
70 61 63 65 2f 0d 0a 0d 0a 63 72 65 61 74 65 20 74 61 62 6c 65 20 74 65 73 74
...
It is the rdba as described in the lob extent map above and references the lid == LOBID. Also the content is correct (this file is an early copy of my scratchpad to this topic). The hwm is 8060 which is smaller as the length of the LOB (11389).
Also nblks is 2, the next block is of some interest as well.
2nd block:
Block dump from disk:
buffer tsn: 4 rdba: 0x0103e58d (4/255373)
scn: 0xa9c.9062fe3c seq: 0x02 flg: 0x04 tail: 0xfe3c0602
frmt: 0x02 chkval: 0xb95a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F98ED985E00 to 0x00007F98ED987E00
...
Block header dump: 0x0103e58d
Object id on Block? Y
seg/obj: 0x6ef63 csc: 0xa9c.9062fe3c itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x01b0.019.00020f33 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x0103e58d]
kdlich [0x7f98ed985e4c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0a9c.9062fe3c
lid 00000001000099bf531d
rid 0x00000000.0000
kdlidh [0x7f98ed985e64 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 3329
spr 0
data [0x7f98ed985e80 52 8060]
This block contains the remaining part of the LOB. hwm shows it contains the remaining bytes.
The new LOB is present in the LOB segment, the old one is still present - exactly as I'd expect as UNDO is handled as copy on write in LOBs. It's only confusing to see no indication (visible to me) that block 0x0103e58b is old and not used anymore.
After some time, (but no activity on the DB) block dumps are created again:
ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_COM_LONGTIME_BLOB"; alter system dump datafile 4 block min 255360 block max 255375; ALTER SESSION SET TRACEFILE_IDENTIFIER = "UPDATE_COM_LONGTIME_EXT2"; alter system dump datafile 4 block min 256512 block max 257023;
Now there is an interesting change:
2nd NGLOB: Hash Bucket
(part of UFS)Block dump from disk: buffer tsn: 4 rdba: 0x0103e583 (4/255363) scn: 0xa9c.916207e5 seq: 0x01 flg: 0x04 tail: 0x07e53d01 frmt: 0x02 chkval: 0xa4e3 type: 0x3d=NGLOB: Hash Bucket Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F9AFDEAE800 to 0x00007F9AFDEB0800 ... Dump of Hash Bucket Block -------------------------------------------------------- Hash Bucket Header Dump Inst:1 Flag:6 Total:0 FSG_COUNT:0 OBJD:454499 Inc:0 Self Descriptive fsg_count:0 Head:0x 0x00000000 Tail:0x 0x00000000 Opcdode:3 Locker Xid: 0x01b0.019.00020f35 Fsbdba: 0x0 Fbrdba: 0x0 Head Cache Entries ------------------- ------------------- Tail Cache Entries ------------------- ------------------- Free Space Chunk Summary Inst:0 Minc:0 Maxc:0 Count:0 Tot:0 MAXC:1 UFS List -------------- Deleted - Chunk:0 DBA: 0x0103e58b Len:140299401691137 Xid: 0x01b0.019.00020f35 Ctime:1504343585 Scn: 0x0a9c.9062fe3c --------------------------------------------------------
No clue why this block changed in between.
Ctime: is epoc for GMT: Saturday, September 2, 2017 9:13:05 AM (when the update was done) and Scn: is the exact SCN when the new LOB was written.
Now it makes some sense, but it's unclear what changed the block between the 2 dumps.
Keine Kommentare:
Kommentar veröffentlichen