/*
 * Title  : shrink_ts.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: To deallocate the space above high watermark in the given tablespace.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 120
set pagesize 0
set pause off

set heading off
set headsep off
set underline off
set colsep " "

set define on
set verify off
set escape off
set embedded on

accept p_ts_name char prompt "Enter Tablespace Name "

spool &&tmp_dir/shrink_ts.tmp

prompt spool &&rpt_dir/shrink_ts.rpt
prompt set echo on
prompt set feedback on

select  
	'alter database datafile ''' || file_name || ''' resize ' || dfsize || 'k;'
from
	(
		select 
			f.file_id,
			f.file_name,
			nvl(ceil((nvl(e.hwm, 0) * (select block_size from dba_tablespaces t,dba_data_files d where t.tablespace_name=d.tablespace_name and f.file_id=e.file_id and rownum<=1))/1024),0) dfsize
		from
			dba_data_files f
		left join(
				select 
					file_id, 
					max(block_id + blocks - 1) hwm 
				from 
					dba_extents 
				group by 
					file_id
			) e on f.file_id = e.file_id
		where
			f.tablespace_name = upper('&&p_ts_name')
	)
where
	dfsize>0
order by
	file_id
/

prompt spool off

spool off

prompt Run the script "&&tmp_dir/shrink_ts.tmp" manually.
prompt Note: Please ensure enough free space available especially if auto extend is turned off.

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80

set termout on
set serverout off
set feedback on
set echo off
