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.

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:

  Table

  Table partition

  Table subpartition

  Index

  Index partition

  Index subpartition

  Cluster

  LOB

  LOB partition

  LOB subpartition

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s