DBMS_SPACE.SPACE_USAGE

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:  http://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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply