set echo off
--
--  Name: objects_wasted_space.sql
--
--  Description: Objects - Show used/unused space information for a specific object 
--         Note: set serveroutput on before running this script.
--
--  Compatability: 7.3.x and above
--
--  Usage: @objects_wasted_space
--  Example: @objects_wasted_space
--
set verify off
set lines 200
VARIABLE total_blocks number;
VARIABLE total_bytes number;
VARIABLE unused_blocks number;
VARIABLE unused_bytes number;
VARIABLE last_used_extent_file_id number;
VARIABLE last_used_extent_block_id number;
VARIABLE last_used_block number;

DECLARE 

vcOwner						varchar2(255);
vcSegmentName				varchar2(255);
vcSegmentType				varchar2(255);

biTotUnused             binary_integer:=0;
biTotBytes              binary_integer:=0;

CURSOR Objects_CURSOR IS 
	select owner,segment_name,segment_type from dba_segments
	where segment_type = 'INDEX' 
	and owner <> 'SYS'
	and bytes > 200000
	order by bytes desc;

begin
	dbms_output.enable(1000000);
   OPEN Objects_CURSOR;
   dbms_output.put_line(
			rpad('OWNER.SEGMENT_NAME',51)||' '||
			rpad('unused_bytes',10)||' '||
			rpad('total_bytes',10)||' '||
			rpad('unused/used bytes',50));
   LOOP
      FETCH Objects_CURSOR INTO vcOwner, vcSegmentName, vcSegmentType;
	   dbms_space.unused_space(vcOwner, vcSegmentName, vcSegmentType, 
                       :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, 
                       :last_used_extent_file_id, :last_used_extent_block_id, :last_used_block);
		if (:unused_bytes/:total_bytes) > .20 then
			dbms_output.put_line(
			rpad(vcOwner,20)||' '||rpad(vcSegmentName,30)||' '||
			rpad(to_char(:unused_bytes),10)||' '||
			rpad(to_char(:total_bytes),10)||' '||
			rpad(to_char((:unused_bytes/:total_bytes),'99.999'),10));
			biTotUnused:=biTotUnused+:unused_bytes;
			biTotBytes:=biTotBytes+:total_bytes;
		end if;
      EXIT WHEN Objects_CURSOR%NOTFOUND;
   END LOOP;
   
   if Objects_CURSOR%ISOPEN = TRUE then
      CLOSE Objects_CURSOR;
   end if;
   
   dbms_output.put_line(
      rpad('Total: ',51)||' '||
      rpad(to_char(biTotUnused),10)||' '||
      rpad(to_char(biTotBytes),10)||' '||
      rpad(' ',10)
      );
      
end;
/
set verify on
set echo on