REM Look for all free extents on tablespace

set pagesize 38
set linesize 110
set verify off
set echo off

select tablespace_name,
status,
contents
from dba_tablespaces;

accept tablespacename char prompt 'Input Tablespace Name : '

column "Data File" format a30
column "Segment Name" format a12
column "Start Block_ID" format 99999999
column "End Block_ID" format 999999
column "Num_blocks" format 999,999,999
column "Size(Mb)" format 999,999.99
column "Size(Mb)" format 999,999.99

break on "Data File"



select substr(a.file_name,1,30) "Data File",
'************' "Segment Name",
b.block_id "Start Block_ID",
b.block_id+b.blocks-1 "End Block_ID",
b.blocks "Num_blocks",
b.bytes/1024 "Size(Kb)",
b.bytes/1024/1024 "Size(Mb)"
from dba_free_space b,dba_data_files a
where b.file_id=a.file_id
and b.tablespace_name=UPPER('&tablespacename')
order by 1,4;
spool off

alter tablespace &tablespacename coalesce;

select substr(a.file_name,1,30) "Data File",
'************' "Segment Name",
b.block_id "Start Block_ID",
b.block_id+b.blocks-1 "End Block_ID",
b.blocks "Num_blocks",
b.bytes/1024 "Size(Kb)",
b.bytes/1024/1024 "Size(Mb)"
from dba_free_space b,dba_data_files a
where b.file_id=a.file_id
and b.tablespace_name=UPPER('&tablespacename')
order by 1,4;