12.1.2.1 The DBMS_SPACE.FREE_BLOCKS procedure

The FREE_BLOCKS procedure returns information about the number of blocks on Oracle's freelist groups for a table, index, or cluster segment. Specifications for Oracle7 and Oracle8 vary as follows.

Here is the Oracle 7.x specification:

PROCEDURE DBMS_SPACE.FREE_BLOCKS
   (segment_owner IN VARCHAR2
   ,segment_name IN VARCHAR2
   ,segment_type IN VARCHAR2
   ,freelist_group_id IN NUMBER
   ,free_blks OUT NUMBER
   ,scan_limit IN NUMBER DEFAULT NULL);

Here is the Oracle 8.0 specification:

PROCEDURE DBMS_SPACE.FREE_BLOCKS
   (segment_owner IN VARCHAR2
   ,segment_name IN VARCHAR2
   ,segment_type IN VARCHAR2
   ,freelist_group_id IN NUMBER
   ,free_blks OUT NUMBER
   ,scan_limit IN NUMBER DEFAULT NULL
   ,partition_name IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

12.1.2.2 The DBMS_SPACE.UNUSED_SPACE procedure

The UNUSED_SPACE procedure returns information about the unused space and the position of the highwater mark in a table, index, or cluster segment. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle 7.x specification:

PROCEDURE DBMS_SPACE.UNUSED_SPACE
   (segment_owner IN VARCHAR2
   ,segment_name IN VARCHAR2
   ,segment_type IN VARCHAR2
   ,total_blocks OUT NUMBER
   ,total_bytes OUT NUMBER
   ,unused_blocks OUT NUMBER
   ,unused_bytes OUT NUMBER
   ,last_used_extent_file_id OUT NUMBER
   ,last_used_extent_block_id OUT NUMBER
   ,last_used_block OUT NUMBER);

Here is the Oracle 8.0 specification:

PROCEDURE DBMS_SPACE.UNUSED_SPACE
   (segment_owner IN VARCHAR2
   ,segment_name IN VARCHAR2
   ,segment_type IN VARCHAR2
   ,total_blocks OUT NUMBER
   ,total_bytes OUT NUMBER
   ,unused_blocks OUT NUMBER
   ,unused_bytes OUT NUMBER
   ,last_used_extent_file_id OUT NUMBER
   ,last_used_extent_block_id OUT NUMBER
   ,last_used_block OUT NUMBER
   ,partition_name IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

12.1.3.1 The segspace package

As usual, the solution to such usability issues lies in creating a package to encapsulate those unwieldy program calls with an easier-to-use layer of programs. Here is the specification for my own package called segspace:

//* Filename on companion disk: segspace.sql */*
CREATE OR REPLACE PACKAGE segspace
   /*
   || Extends the DBMS_SPACE package by creating function
   || calls to return individual parameter values
   ||
   || Author:  John Beresniewicz, Savant Corp
   || Created: 07/29/97
   ||

   || Compilation Requirements: 
   ||
   || Execution Requirements:
   ||
   || ANALYZE ANY system privilege
   ||
   */
AS

   /*
   || sets the specified segment as current context
   */
   PROCEDURE set_segment
      (name_IN IN VARCHAR2
      ,type_IN IN VARCHAR2
      ,schema_IN IN VARCHAR2
      ,partition_IN IN VARCHAR2 DEFAULT NULL);

   /* returns current segment name */
   FUNCTION current_name RETURN VARCHAR2;

   /* returns current segment type */
   FUNCTION current_type RETURN VARCHAR2;

   /* returns current segment schema */
   FUNCTION current_schema RETURN VARCHAR2;

   /* 
   || returns total_blocks from DBMS_SPACE.UNUSED_SPACE
   || for the segment specified
   */
   FUNCTION total_blocks
      (name_IN IN VARCHAR2 DEFAULT current_name
      ,type_IN IN VARCHAR2 DEFAULT current_type
      ,schema_IN IN VARCHAR2 DEFAULT current_schema)
   RETURN NUMBER;

   
   /* 
   || returns unused_blocks from DBMS_SPACE.UNUSED_SPACE
   || for the segment specified
   */
   FUNCTION unused_blocks
      (name_IN IN VARCHAR2 DEFAULT current_name
      ,type_IN IN VARCHAR2 DEFAULT current_type
      ,schema_IN IN VARCHAR2 DEFAULT current_schema)
   RETURN NUMBER;


   /* 
   || returns number of blocks on segment freelist using
   || DBMS_SPACE.FREE_BLOCKS
   */
   FUNCTION freelist_blocks
      (name_IN IN VARCHAR2 DEFAULT current_name
      ,type_IN IN VARCHAR2 DEFAULT current_type
      ,schema_IN IN VARCHAR2 DEFAULT current_schema
      ,freelist_group_IN IN NUMBER DEFAULT 0
      ,partition_IN IN VARCHAR2 DEFAULT NULL)
   RETURN NUMBER;

END segspace;

The segspace package has functions called total_blocks and unused_blocks. These functions both accept segment identification information as IN parameters and return the value of their corresponding OUT parameters from DBMS_SPACE.UNUSED_SPACE. So in SQL*Plus, you can use these functions as follows:

SQL> var tot_blks NUMBER
SQL> execute :tot_blks := segspace.total_blocks('TENK','TABLE','LOAD1');

PL/SQL procedure successfully completed.

SQL> print tot_blks

 TOT_BLKS
---------
      455

Well, this sure is a lot easier than calling the UNUSED_SPACE procedure directly! Notice, however, that the IN parameters to these functions also all have default values, which means that they can be suppressed when making the function call (as long as the default is the desired value). The default values used are the segment identifiers (name, type, and schema) most recently specified. Thus we can find out the unused blocks for the LOAD1.TENK table by immediately following the preceding call with this:

SQL> var unused_blks number
SQL> execute :unused_blks := segspace.unused_blocks;

PL/SQL procedure successfully completed.

SQL> print unused_blks

UNUSED_BLKS
-----------
         10

By retaining the current segment context set by the previous call and using default values, a complex procedure call (UNUSED_SPACE) with ten parameters is transformed into a simple function call requiring only a target variable for its result.

Now, this is something I might be able (and want) to actually use.

The set_segment procedure is used to set the current segment context, which amounts to establishing the default IN parameters for all the functions.

Astute readers will suspect that private package globals play a part in this trickery, and they are correct. They may also raise questions about the performance implications of splitting the OUT parameters of UNUSED_SPACE into individual function calls, as this is a relatively "expensive" procedure call to make and should not be redundantly or needlessly invoked. Well, segspace is designed to be both useful and efficient.

Here is the package body for segspace (an explanation follows the code):

//* Filename on companion disk: segspace.sql */*
CREATE OR REPLACE PACKAGE BODY segspace
AS

   /* record type to hold data on segment */
   TYPE segdata_rectype IS RECORD
      (name    VARCHAR2(30)
      ,schema  VARCHAR2(30) DEFAULT USER
      ,type    VARCHAR2(30) DEFAULT 'TABLE'
      ,partition  VARCHAR2(30) DEFAULT NULL
      ,total_blocks  NUMBER
      ,total_bytes   NUMBER
      ,unused_blocks NUMBER
      ,unused_bytes  NUMBER
      ,last_extent_file  NUMBER
      ,last_extent_block NUMBER
      ,last_block    NUMBER
      ,last_segload  DATE := SYSDATE - 1
      );

   /* global rec for current segment data */
   segdata_rec  segdata_rectype;

   /* reload timeout in seconds */
   segload_timeout   INTEGER := 60;

   /* flag for new segment */
   newseg_TF   BOOLEAN := TRUE;

   /* 
   || returns the segment name from segdata_rec
   */
   FUNCTION current_name RETURN VARCHAR2
   IS
   BEGIN
      RETURN segdata_rec.name;
   END current_name;

   /* 
   || returns the segment type from segdata_rec
   */
   FUNCTION current_type RETURN VARCHAR2
   IS
   BEGIN
      RETURN segdata_rec.type;
   END current_type;

   /* 
   || returns the segment schema from segdata_rec
   */
   FUNCTION current_schema RETURN VARCHAR2
   IS
   BEGIN
      RETURN segdata_rec.schema;
   END current_schema;

   /*
   || sets specific segment as context
   */
   PROCEDURE set_segment
      (name_IN IN VARCHAR2
      ,type_IN IN VARCHAR2
      ,schema_IN IN VARCHAR2
      ,partition_IN IN VARCHAR2 DEFAULT NULL)
   IS
   BEGIN
      /* check if new segment and set flag */
      IF ( segdata_rec.schema != schema_IN
        OR segdata_rec.name   != name_IN
        OR segdata_rec.type   != type_IN
        OR segdata_rec.partition != partition_IN
          )
      THEN
         newseg_TF := TRUE;
      ELSE
         newseg_TF := FALSE;
      END IF;

      /* set segment globals */
      segdata_rec.schema := schema_IN;
      segdata_rec.name   := name_IN;
      segdata_rec.type   := type_IN;
      segdata_rec.partition := partition_IN;
   END set_segment;


   FUNCTION reload_TF RETURN BOOLEAN
   IS
   /*
   || returns TRUE if timed out or new segment since last load
   */
   BEGIN
      RETURN ( SYSDATE > segdata_rec.last_segload +
                          segload_timeout/(24*60*60) 
             )
           OR newseg_TF;
   END reload_TF;


   PROCEDURE load_unused
   IS
   /*
   || loads segment unused space data for current segment using 
   || DBMS_SPACE.UNUSED_SPACE if the segment is new or timeout limit
   || reached since last load
   */
   BEGIN
      IF reload_TF
      THEN
         DBMS_SPACE.UNUSED_SPACE
            (segment_owner => segdata_rec.schema
            ,segment_name  => segdata_rec.name
            ,segment_type  => segdata_rec.type
            ,total_blocks  => segdata_rec.total_blocks
            ,total_bytes   => segdata_rec.total_bytes
            ,unused_blocks => segdata_rec.unused_blocks
            ,unused_bytes  => segdata_rec.unused_bytes
            ,last_used_extent_file_id => segdata_rec.last_extent_file
            ,last_used_extent_block_id => segdata_rec.last_extent_block
            ,last_used_block  => segdata_rec.last_block
         /* -------------------------------------------- */
         /* NOTE: uncomment following line for Oracle 8  */
         /* -------------------------------------------- */
         /* ,partition_name  => segdata_rec.partition */
            );

         segdata_rec.last_segload := SYSDATE;
      END IF;
   END load_unused;


   FUNCTION total_blocks
      (name_IN IN VARCHAR2
      ,type_IN IN VARCHAR2
      ,schema_IN IN VARCHAR2)
   RETURN NUMBER
   IS
   /* 
   || sets current segment and calls load_unused
   */
   BEGIN
      set_segment(name_IN, type_IN, schema_IN);
      load_unused;
      RETURN segdata_rec.total_blocks;
   END total_blocks;


   FUNCTION unused_blocks
      (name_IN IN VARCHAR2
      ,type_IN IN VARCHAR2
      ,schema_IN IN VARCHAR2)
   RETURN NUMBER
   IS
   /* 
   || sets current segment and calls load_unused
   */
   BEGIN
      set_segment(name_IN, type_IN, schema_IN);
      load_unused;
      RETURN segdata_rec.unused_blocks;
   END unused_blocks;


   /* 
   || returns number of blocks on segment freelist using
   || DBMS_SPACE.FREE_BLOCKS
   */
   FUNCTION freelist_blocks
      (name_IN IN VARCHAR2 DEFAULT current_name
      ,type_IN IN VARCHAR2 DEFAULT current_type
      ,schema_IN IN VARCHAR2 DEFAULT current_schema
      ,freelist_group_IN IN NUMBER DEFAULT 0
      ,partition_IN IN VARCHAR2 DEFAULT NULL)
   RETURN NUMBER
   IS
      /* variable to hold output from call to FREE_BLOCKS */
      temp_freelist_blocks NUMBER;

      /*
      || loads segment freelist size using DBMS_SPACE.FREE_BLOCKS
      || scan limit NULL means no limit
      */
      BEGIN
         DBMS_SPACE.FREE_BLOCKS
            (segment_owner => schema_IN
            ,segment_name  => name_IN
            ,segment_type  => type_IN
            ,freelist_group_id => freelist_group_IN
            ,free_blks => temp_freelist_blocks
            ,scan_limit => NULL
         /* -------------------------------------------- */
         /* NOTE: uncomment following line for Oracle 8  */
         /* -------------------------------------------- */
         /* ,partition_name  => partition_IN          */
            );
      
      RETURN temp_freelist_blocks;
   END freelist_blocks;

END segspace;

The segspace package body declares a record type called segdata_rectype and a private global of that type called segdata_rec. This record is designed to hold a copy of all parameters (both IN and OUT) used by the UNUSED_SPACE procedure. The name, schema, type, and partition fields in segdata_rec correspond to the IN parameters of UNUSED_SPACE. These are set using the set_segment procedure. Think of this as the current segment context -- the segment currently being analyzed. The functions current_name, current_schema, and current_type simply return the corresponding elements of the current segment context.

The load_unused procedure is the one that actually calls UNUSED_SPACE. It takes as IN parameters the appropriate field values from segdata_rec, and assigns its OUT values to the corresponding fields in segdata_rec. Now the individual OUT parameters from UNUSED_SPACE can be exposed through individual function calls that return fields from segdata_rec.

So the basic logic is quite simple:

1. The set_segment procedure establishes a segment context in segdata_rec.

2. The load_unused procedure loads UNUSED_SPACE information for the current segment into segdata_rec.

3. Individual field values from segdata_rec are returned through functions such as total_blocks and unused_block.

Now, a three-step process to retrieve the data items individually does not really represent an increase in usability, so what really happens is that the functions total_blocks and unused_blocks each do all three steps.

   FUNCTION unused_blocks
      (name_IN IN VARCHAR2
      ,type_IN IN VARCHAR2
      ,schema_IN IN VARCHAR2)
   RETURN NUMBER
   IS
   /* 
   || sets current segment and calls load_unused
   */
   BEGIN
      set_segment(name_IN, type_IN, schema_IN);
      load_unused;
      RETURN segdata_rec.unused_blocks;
   END unused_blocks;

Remember that calling DBMS_SPACE.UNUSED_SPACE is relatively expensive, and we want to avoid calling it more often than necessary. It would be nice to be able to do the following without calling UNUSED_SPACE twice:

BEGIN
   pct_free := 100*
               (segspace.unused_blocks('TABLENAME','TABLE','SCHEMA') /
                segspace.total_blocks('TABLENAME','TABLE','SCHEMA') 
                );
END;

The load_unused procedure avoids calling UNUSED_SPACE too often by checking a function called reload_TF, which returns a BOOLEAN indicating whether to reload segment data. The reload_TF function will return TRUE (reload) if either of the following is TRUE:

• It has been longer than segload_timeout seconds since the last call to DBMS_SPACE.LOAD_UNUSED.

• The current segment context is different than the context for the last call to DBMS_SPACE.LOAD_UNUSED.

Thus the previous PL/SQL block will call UNUSED_SPACE at most once (and perhaps not at all, if it was recently called for the same segment). The private global newseg_TF is a BOOLEAN flag indicating a new context. This is maintained by the set_segment procedure: whenever a context is established, the flag is set to TRUE, if it is a new context.

Additional usability in the total_blocks and unused_blocks functions is achieved by using default values for the IN parameters and careful ordering of the parameters. The default values for name_IN, type_IN, and schema_IN are assigned by the functions current_name, current_type, and current_schema. The parameters are ordered such that the most likely to change (name) is first, followed by type, and then schema. This is based on the reasonable assumption that when doing space analysis, the user will probably do all tables by schema or all indexes by schema. Additionally, the initial default prior to setting a context at all is the current user schema and segment type TABLE.

Q: Q: Why did I use functions for the default values instead of direct reference to segdata_rec?

A: A: In order to directly reference the segdata_rec components for parameter defaults in the package specification, I would have also had to declare segdata_rec in the specification. This would expose segdata_rec such that it could be inadvertently modified by other programs. By using functions for the default values, segdata_rec can be declared privately (and thus protected) in the package body.

A: The function freelist_blocks simply calls DBMS_SPACE.FREE_BLOCKS and returns its single OUT parameter free_blks. Because FREE_BLOCKS has only a single OUT parameter, it was not really necessary to implement the optimizations discussed previously to avoid redundant calls. The function does improve usability by supplying defaults for the IN parameters to FREE_BLOCKS, reducing the calling profile where the defaults are correct. Be careful, though, because freelist_blocks does not do a set_segment to establish a context. If it is used alone (i.e., not in conjunction with total_blocks or unused_blocks), it is best to specify a full segment context in the call.

Q: Q: Why did I choose to not have freelist_blocks call set_segment to establish a segment context?

A: A: The unused_blocks function relies on segdata_rec to provide information about the current segment context loaded by the load_unused procedure. If the freelist_blocks function were to call set_segment to establish a context, it would also have to call load_unused to keep segdata_rec in synch with the context. This would introduce significant and unnecessary overhead, when only freelist information is desired.

Here is an example of using the segspace package in a SQL*Plus script to report on unused space in segments for a specific tablespace:

/* Filename on companion disk: spcex2.sql */*
undefine tablespace_name
set serveroutput on size 100000
set verify off

DECLARE
   total_blocks NUMBER :=0;
   unused_blocks NUMBER :=0;
BEGIN
   DBMS_OUTPUT.PUT_LINE('TABLESPACE: '||UPPER('&&tablespace_name'));
   FOR seg_rec IN 
      (SELECT segment_name, segment_type, owner
         FROM dba_segments
        WHERE tablespace_name = UPPER('&&tablespace_name') )
   LOOP
      total_blocks := total_blocks +
                        segspace.total_blocks
                           (seg_rec.segment_name
                           ,seg_rec.segment_type
                           ,seg_rec.owner);

      unused_blocks := unused_blocks + segspace.unused_blocks;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Total Blocks: '||TO_CHAR(total_blocks));
   DBMS_OUTPUT.PUT_LINE('Unused Blocks: '||TO_CHAR(unused_blocks));
   DBMS_OUTPUT.PUT_LINE('Pct Unused: '||
            TO_CHAR(ROUND(unused_blocks/total_blocks*100)) );
END;
/

This is sample output from executing the script:

Enter value for tablespace_name: LOAD_DATA
TABLESPACE: LOAD_DATA
Total Blocks: 9195
Unused Blocks: 1300
Pct Unused: 14

PL/SQL procedure successfully completed.

I like using segspace to probe space utilization within segments. With segspace, I can obtain useful reports like the previous one with a few quick lines. Direct use of DBMS_SPACE would require much more time and effort.