Found a cool package and procedure while working on a problem today.
DBMS_SPACE.SPACE_USAGE
This takes a segment and shows how many blocks are free or partially filled.
In our case we hit a bug which resulting in tons of unformatted blocks and this was the recommended fix.
Oracle note 729149.1 describes the issue and shows an example use of DBMS_SPACE.SPACE_USAGE.
Here is a simple script and log: https://www.bobbydurrettdba.com/uploads/space_usage.zip
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.
– Bobby
