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>
Hey Paul,
Start dump data blocks tsn: 5 file#:12 minblk 483 maxblk 483
...
76543210
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
Independent Oracle performance consultant and researcher
Homepage: http://www.soocs.de
> Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> hat am 24. Januar 2017 um 12:13 geschrieben:
not quite sure if we are talking about the same, but the total amount of available space is also included in the block dump. Here is just a short demo
from 12.1.0.2
-------------------8<---------------------
SQL> alter system dump datafile 12 block 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
flag=--------
ntab=1
nrow=68
frre=-1
fsbo=0x9a
fseo=0x3ca
avsp=0x330
tosp=0x330
...
-------------------8<---------------------
Stefan Koehler
Twitter: _at_OracleSK
>
> 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-l
Received on Tue Jan 24 2017 - 17:06:14 CET