Just throwing out a brief comment (one of my many draft notes that I don’t have time to complete) about the dbms_space package. You’re probably familiar with this package and how, for ASSM segments, it can give you a measure of the available space in the blocks in a data segment, reporting 6 possible states of the blocks below the high high water mark (HHWM) e.g.:
Unformatted : 12 Freespace 1 ( 0 - 25% free) : 0 Freespace 2 ( 25 - 50% free) : 14 Freespace 3 ( 50 - 75% free) : 0 Freespace 4 ( 75 - 100% free) : 0 Full : 34
The above is the output from a little procedure I wrote to wrap around a call to the procedure dbms_space.space_usage().
But when you look at indexes what does it mean, in the context of this package, to talk about index blocks having free space ? On one hand you might want to get an idea of how many blocks you would need if you rebuilt the index with pctfree set either to zero or the current value; on the other hand there are only two (or maybe 3) interesting states for index blocks - in use or completely empty (the third state would be unformatted). The implications of this came up in a conversation on Oracle-L : Oracle doesn't actually use this procedure to tell you about the free space in the index leaf blocks, it only reports three possible states .
The sample output above comes from an index where I've been inserting and deleting rows singly and in batches from a couple of different sessions - carefully counting index entries in blocks - after rebuilding the index once. As you can see the package reports only 3 possible states: unformatted, FS2, and FULL. Following the conversation on Oracle-L I had thought that FS2 probably meant "empty and available to be used elsewhere in the index" but this turned out to be wrong - at present I have no idea when Oracle decides that a block has become full, or when it decides that it's no longer full; all I can say from the above report is that this index has 48 index leaf blocks below the HHWM, and at some time 34 of them have been "full".
When I generated this report 19 of the blocks were in their original state (from when I had done an index rebuild - and that's a case where index blocks DO become full), some were completely empty, some were at 30% of the notional "full" state, some were at 66% of "full".
Further investigations are left as an exercise for enthusiastic readers.