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 	s_tabsp_detail.sql
REM
REM <SQLDIR_GRP>TABSP MOST</SQLDIR_GRP>
REM 
REM Author:
REM 	Kevin Loney "ORACLE8 DBA Handbook" 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script is from Kevin Loney "ORACLE8 DBA Handbook"
REM	----------------------------------------------------------------------
REM	Generates a mapping of the space usage
REM	(free space vs used) in a tablespace. It graphically
REM	shows segment and free space fragmentation.
REM
REM	After running this script:
REM	if a tablespace has too many "free space gaps" you should:
REM	a) let SMON process coalesce extents by setting default PCTINCREASE to
REM	   nonzero value
REM	OR
REM	b) manually coalesce the free extents of the tablespace:
REM	   SQL> alter tablespace <tablespace_name> coalesce;
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tabsp_detail.sql
REM 
REM Example:
REM	s_tabsp_detail.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set trims on
set pages 60
set lines 132
set verify off

col file_id heading "File|id"

select		'free space' owner		/*"owner" of free space*/
,		'         ' object		/*blank object name*/
,		file_id				/*file id for the extent header*/
,		block_id			/*block id for the extent header*/
,		blocks				/*length of the extent, in blocks*/
,               bytes
from		dba_free_space
where		tablespace_name = upper('&&1')
union
select		substr(owner,1,20)		/*owner name (first 20 chars)*/
,		substr(segment_name,1,32)	/*segment name*/
,		file_id				/*file id for the extent header*/
,		block_id			/*block id for the extent header*/
,		blocks				/*length of the extent, in blocks*/
,               bytes
from 		dba_extents
where		tablespace_name = upper('&&1')
order by	3,4

spool s_tabsp_detail..&&1..log
/
spool off
undefine 1