Re: Identifying free space in a block
Date: Tue, 24 Jan 2017 17:06:14 +0100 (CET)
Message-ID: <764859873.1840858.1485273974857.JavaMail.open-xchange_at_app04.ox.hosteurope.de>
-------------------8<---------------------SQL> alter system dump datafile 12 block 483;
Start dump data blocks tsn: 5 file#:12 minblk 483 maxblk 483
...
Object id on Block? Y
seg/obj: 0x13d8e csc: 0x00000000004204c8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x30001e0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x00000000004204c8
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x030001e3
data_block_dump,data header at 0x7fb6e23bbe74
tsiz: 0x1f80
hsiz: 0x9a
pbl: 0x7fb6e23bbe74
76543210
flag=-------- ntab=1 nrow=68 frre=-1 fsbo=0x9a fseo=0x3ca avsp=0x330 tosp=0x330
...
-------------------8<---------------------
avsp = The amount of space available in this block for new entries. Does not include any space another transaction using this block has just freed due to a delete or update. This will be reflected the next time a total cleanout occurs. This value is compared with PCTFREE and PCTUSED to determine if the block should belong to a freelist.
tosp = The total amount of space available in this block. If this value is different from the ‘avsp’ amount, it should be equal to the ‘avsp’ plus any free space credits within the ITL list. An Interested Transaction List (ITL) is present in every block header and is used to associate changes to rows with a particular transaction. The ITL is composed of an index number, transaction identifier (undo segment number, transaction table slot number, and wrap or version number of the slot), commit SCN, and free space credit. The free space credit tracks the amount of space in the block released by a transaction (updates or deletes)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> hat am 24. Januar 2017 um 12:13 geschrieben:
>
> Hi
>
> How do I identify how much free space is in a specific block? I know that the ITL can grow if there is space in the block. I have dumped the block
> and can see there are 5 entries, so how can I tell if a 6th entry could be created?
>
> I don't believe it needs to grow, I am just curious.
>
> Oracle 12.1.0.2 Linux x86_64
>
> Thanks
>
> Paul Houghton
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 24 2017 - 17:06:14 CET