/**********************************************************************
 * File:        shrink_files.sql
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        05-Jan-99
 *
 * Description:
 *      SQL*Plus script to display amount of unused space in
 *	datafiles belonging to a particular tablespace.  The
 *	value for "highest_mb" is the lowest value you can "shrink"
 *	the datafile to... 
 *
 *	Please note that the V_BLK_DIV substitution variable assumes
 *	an 8Kb block size for the tablespace in question.  Please
 *	change accordingly if the blocksize of the database/tablespace
 *	is not 8Kb...
 *
 * Modifications:
 *********************************************************************/
set pagesize 100 linesize 100 trimout on trimspool on
set feedback off verify off echo off feedback off timing off
col file_name format a50
col sum_mb format 99,990.00
col highest_mb format 9,990.00
col tot_mb format 9,990.00

/* #-of-blocks divided by V_BLK_DIV equals Mbytes */
define V_BLK_DIV=128	/* value of 128 implies DB_BLOCK_SIZE = 8192 */
define V_TS=&&1		/* name of tablespace to report upon */

select	f.file_name,
	sum(nvl(e.blocks,0))/128 sum_mb,
	max(nvl(e.block_id,0)+nvl(e.blocks,0))/128 highest_mb,
	f.blocks/128 tot_mb
from	dba_extents e,
	dba_data_files f
where	f.tablespace_name = upper('&&V_TS')
and	e.tablespace_name (+) = f.tablespace_name
and	e.file_id (+) = f.file_id
group by f.file_name,
	 f.blocks/128

spool shrink_files_&&V_TS
/
spool off
set verify on linesize 80
undef V_TS
undef V_BLK_DIV