Oracle Pipelined Function for DBMS_SPACE.FREE_USAGE

This is a pipelined function to show dbms_space.space_usage output in a table instead of using dbms_output.

dbms_space.space_usage will show you the details of blocks between LHWM (Low High Water Mark) and HHWM (High High Water Mark). Based on the output of this function, you can decide whether to shrink the segment or not.

There are many online resources to explain pipelined functions so I will not do this here.

Here is the code:

-- create object
create type space_usage_object as object 
(
   Segment_owner varchar2(30),
   segment_name varchar2(30),
   segment_type varchar2(30),
   partition_name varchar2(30),
   blocks_unformatted number,
   bytes_unformatted number,
   
   blocks_0_25 number,
   bytes_0_25 number,   

   blocks_25_50 number,
   bytes_25_50 number,   
   
   blocks_50_75 number,
   bytes_50_75 number,   
   
   blocks_75_100 number,
   bytes_75_100 number,            
   
   blocks_full number,
   bytes_full number
);


-- create type
CREATE TYPE space_usage_type AS TABLE OF space_usage_object;   

-- pipelined function
create or replace FUNCTION SPACE_USAGE (
  in_segment_owner        IN  VARCHAR2 ,
  in_segment_name  IN  VARCHAR2 ,
  in_segment_type  IN  VARCHAR2 ,
  in_partition_name       IN  VARCHAR2 := null)
  RETURN space_usage_type PIPELINED AS

 v_unformatted_blocks number;
 v_unformatted_bytes number;
 v_fs1_blocks number;
 v_fs1_bytes number;
 v_fs2_blocks number;
 v_fs2_bytes number;
 v_fs3_blocks number;
 v_fs3_bytes number;
 v_fs4_blocks number;
 v_fs4_bytes number;
 v_full_blocks number;
 v_full_bytes number;
 
 
begin


  dbms_space.space_usage(segment_owner => in_segment_owner,
                        segment_name => in_segment_name,
                        segment_type => in_segment_type,  
                        unformatted_blocks => v_unformatted_blocks, 
                        unformatted_bytes => v_unformatted_bytes,
                        fs1_blocks => v_fs1_blocks, 
                        fs1_bytes => v_fs1_bytes,
                        fs2_blocks => v_fs2_blocks,
                        fs2_bytes => v_fs2_bytes,
                        fs3_blocks => v_fs3_blocks,
                        fs3_bytes => v_fs3_bytes,
                        fs4_blocks => v_fs4_blocks, 
                        fs4_bytes => v_fs4_bytes,
                        full_blocks => v_full_blocks, 
                        full_bytes => v_full_bytes,
                        partition_name =>  in_partition_name);
                        
  pipe row (space_usage_object (in_segment_owner, in_segment_name, in_segment_type, in_partition_name,
                              v_unformatted_blocks , v_unformatted_bytes, 
 v_fs1_blocks, v_fs1_bytes, v_fs2_blocks,  v_fs2_bytes,  v_fs3_blocks, 
 v_fs3_bytes, v_fs4_blocks , v_fs4_bytes , v_full_blocks , v_full_bytes ));

  RETURN ;
END;


-- selecting 
select * from table(space_usage ('OWNER', 'SEGMENT_NAME', 'SEGMENT_TYPE'));

Hazem Ameen
Senior Oracle DBA