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_all.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 ALL datafiles
REM	by resizing it ...  Coalesces ALL tablespace prior resize.
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_dbf_trim_all.sql
REM
REM Example:
REM	c_dbf_trim_all.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

set pages 0
set feedback off
set lines 300
set trims on
col l format a300

SELECT 'ALTER TABLESPACE '||tablespace_name||' COALESCE;' l
FROM   dba_tablespaces

spool c_dbf_trim_all.sql.run
/
spool off

set feedback on

@c_dbf_trim_all.sql.run

set feedback off

SELECT 'ALTER DATABASE DATAFILE '||chr(39)||dbf.file_name||chr(39)||' RESIZE '||LTRIM(TO_CHAR((dbf.bytes-free.bytes)/1024,'999999999999'))||'K ;'  l
,      '--ALTER DATABASE DATAFILE '||chr(39)||dbf.file_name||chr(39)||' AUTOEXTEND ON NEXT 12m MAXSIZE '||LTRIM(TO_CHAR((dbf.bytes)/1024,'999999999999'))||'K ;'   l
FROM dba_data_files dbf
,    (SELECT file_id
      ,      bytes
      FROM   dba_free_space dbf
      WHERE  block_id IN (SELECT MAX(BLOCK_ID)
                          FROM   dba_free_space
                          WHERE  tablespace_name = dbf.tablespace_name
                          GROUP BY file_id)      ) free
WHERE free.file_id = dbf.file_id

spool c_dbf_trim_all.sql.run
/
spool off

ed c_dbf_trim_all.sql.run

set feedback on