This script will help you find the space reclaimable in your datafiles, it finds the High Water Mark of all your datafiles (the minimum size) and then report the following information:

• Datafile Size
• Datafile HWM
• Percentage of space reclaimable
• Command to resize the datafile
• Total space reclaimable in your datafiles
• Percentage of space reclaimable in your datafiles

Find space reclaimable in the datafiles

set pages 1000
set serveroutput on
set lines 500

DECLARE 
		
	v_hwm number :=0;
	v_current_size number :=0;
	v_percent_gain number :=0;
	v_total_space_rec number :=0;
	v_total_data_size number :=0;
		
BEGIN
	
	for v_file_info in (select FILE_NAME, FILE_ID, BLOCK_SIZE 
						from dba_tablespaces tbs, dba_data_files df 
						where tbs.tablespace_name = df.tablespace_name)
	loop
		select ceil( (nvl(hwm,1) * v_file_info.block_size)/1024/1024 ) ,
			   ceil( blocks * v_file_info.block_size/1024/1024) into v_hwm,v_current_size
		from dba_data_files a,
		( select file_id, max(block_id+blocks-1) hwm
		  from dba_extents
		  group by file_id ) b
	   where a.file_id = b.file_id(+)
	   and a.file_id = v_file_info.file_id;
	   
	   v_total_space_rec := v_total_space_rec +(v_current_size-v_hwm);
	   v_total_data_size := v_total_data_size +v_current_size;
		  
		dbms_output.put_line(v_file_info.file_name || ':');
		dbms_output.put_line('Current size: ' || v_current_size || 'M' );
		dbms_output.put_line('HWM: ' || v_hwm || 'M' );
		dbms_output.put_line('Percentage reclaimable: ' || round((v_current_size-v_hwm)*100/v_current_size,2) || '%');
		dbms_output.put_line('Use following command to resize: ALTER DATABASE DATAFILE ''' || v_file_info.file_name || ''' RESIZE ' || v_hwm|| 'M;');
		
		dbms_output.put_line('	');
		dbms_output.put_line('	');
	end loop;
	
	dbms_output.put_line('Total datafiles size reclaimable: ' || v_total_space_rec || 'M');
	dbms_output.put_line('Percentage of space reclaimable in the datafiles: ' || round(v_total_space_rec*100/v_total_data_size,2) || '%');
END;
/