REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM 	c_dbf_trim.sql
REM
REM <SQLDIR_GRP>MAINT DBF</SQLDIR_GRP>
REM
REM Author:
REM 	Vitaliy Mogilevskiy
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	trims the last FREE CHUNK from a datafile
REM	by resizing it ...  Coalesces tablespace prior resize.
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_dbf_trim.sql
REM
REM Example:
REM	c_dbf_trim.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM	12-20-2001	VMOGILEV	fixed a bug with file_id missing in the IN clause
REM                                     fixed bug when a file had blocks taken above
REM                                       the last free block this was causing ORA-03297
REM                                     put ORDERED hint to improve performance
REM                                       if you don't care about ORA-03297 on files where
REM                                       last free block is not really last then you
REM                                       can remove subquery to dba_extents it runs much
REM                                       faster then ...
REM
REM

set verify off
set trims on
set pages 0
set feedback on

accept 1 prompt "Enter tablespace name to work with: "

PROMPT coalescing tablespace &&1 ...
ALTER TABLESPACE &&1 COALESCE;

set feedback off

SELECT /*+ ORDERED */ 
'ALTER DATABASE DATAFILE '||chr(39)||dbf.file_name||chr(39)||
   ' RESIZE '||LTRIM(TO_CHAR((dbf.bytes-free.bytes)/1024,'999999999999'))||'K ;'
FROM (SELECT fr.file_id
      ,      fr.bytes
      FROM   dba_free_space fr
      WHERE  (file_id, block_id) IN
                         (SELECT file_id, MAX(BLOCK_ID)
                          FROM   dba_free_space
                          WHERE  tablespace_name = UPPER('&&1')
                          GROUP BY file_id)
      AND  block_id > (SELECT MAX(block_id) 
                       FROM dba_extents
                       WHERE file_id = fr.file_id)) free
,    dba_data_files dbf
WHERE free.file_id = dbf.file_id


spool c_dbf_trim.run
/
spool off

ed c_dbf_trim.run
set feedback on