Found a cool package and procedure while working on a problem today.
This takes a segment and shows how many blocks are free or partially filled.
Here is the output of the PL/SQL block:
Total number of blocks that are unformatted: 0 Number of blocks that has at least 0 to 25% free space: 0 Number of blocks that has at least 25 to 50% free space: 0 Number of blocks that has at least 50 to 75% free space: 0 Number of blocks that has at least 75 to 100% free space: 5 Total number of blocks that are full in the segment: 0
The example is a small table with the minimum 5 blocks and only 1 row. The output says that the 5 blocks all have at least 75% of their space free.
he space_usageprocedure returns the total used space within a table of index, all rows up to the High Water Mark (HWM). The space_usageprocedure can only be used in tables/indexes that have been created in a tablespace with auto segment space management (ASSM or ?bitmapped freelists). The types of segments that can be analyzed by the space_usageprocedure include:
To understand how the space_usage procedure works, consider a table called tab_dbms_space_usage which receives many insert and delete operations.
Because the delete operations leave empty space, the space_usage procedure can be used to verify the lost space in this table and justify as reorganization of the table. A table can be reorganized in a variety of ways.