Here’s a little puzzle if you have nothing better to do between now and the new year. The following extract came from 18.104.22.168, but could have come from 10.2.0.5 or 22.214.171.124 (and many others). I’ve got a simple heap table where the last thing I (or anyone) did was “alter table t1 move” before dumping the first (data) block of the table. Looking at the resulting trace file, I see the following:
fsbo=0x56e fseo=0xf4d avsp=0x5f tosp=0x5f
If you need to have the acronyms decoded they are (according to my best guess):
fsbo – free space, beginning offset
fseo – free space, end offset
avsp – available space
tosp – total space
Doing the arithmetic, the free space starts at offset 0x56e and ends at 0xf4d, which means the free space gap is 2,527 bytes; but the total space available for use is only 0x5f bytes, i.e. 95 bytes. So what has happened to the other 2,432 ?
Remember – I dumped the block immediately after issuing “alter table t1 move”, so there are no issues of delayed block cleanout, uncommitted transactions etc. to worry about.
Footnote: the reason why you have “available space” and “total space” is to keep track of the space made available by deleted rows. The “avsp” (usually) reports the size of the gap between the row directory and the row heap; the “tosp” includes the space in the holes left in the row heap after rows have been deleted (or updated in a way that moves them up to the top of the heap, leaving a gap behind them, or updated in situ in a way that reduces the row length leaving a little hole).