spool map_tsps_free.rslt 

set linesize 120;
set pagesize 1000;
set long 50;
set echo on;

break on "TBL SPACE" on "OWNER";

/* ****************************************************** */
/*                                                        */
/* Name:    map_tsps_free.sql                             */
/* Purpose: show the contents of all tablespaces within   */
/*          the database; based on contiguous space       */
/* Creator: R.Kupcunas                                    */
/* Date:    25-Jan-99                                     */
/*                                                        */
/* ****************************************************** */

set echo off;
spool map_tsps_free.rslt
column bytes format 999,999,999.99 heading MBYTES
column substr(f.tablespace_name,1,10) heading TABLESPACE alias tablespace
column substr(file_name,22,12) heading DATAFILE alias file_name
break on tablespace on file_name
        
select substr(f.tablespace_name,1,10)					"TABLESPACE",
       substr(file_name,40,16)						"FILE NAME",
       f.block_id								"BLOCK",
       'free space' 							owner,
       '   ' 								object,
       substr(to_char(f.bytes/(1024*1024),'999,999.99'),1, 14)	"MBytes"
from sys.dba_free_space f, sys.dba_data_files d
where f.tablespace_name = upper('&&tblspc')
and f.file_id = d.file_id
order by 6 asc, 1,2,3;

spool off;
undefine tblspc;
set echo on;









