/*
#########################################################
#							#
# List the file name and size of all datafiles in       #
# selected tablespace.					#
#							#
# The tablespace can be inserted as the parameter or	#
# the scipt will prompt for it.				#
# Example @datafilesintbs system			#
#							#
#########################################################
*/

set lines 250
set pages 300
set numwidth 11
column "File" format a37
column "Mount Point" format a50
column "Tablespace" format a10
column username format a10
column osuser format a10
column "SQL" format a64
column sid format 999
column serial# format 999999
column cmd format 999
column taddr format a8
column lockwait format a8
column machine format a20
column program format a30
set verify off

select df.file_name "File",round(df.bytes/1024/1024,1) "Total MB",df.autoextensible "Auto",round(df.maxbytes/1024/1024,1)
"Max",df.increment_by "Incr",
round(fs.sum_bytes/1024/1024,1) "Free MB",round(fs.max_bytes/1024/1024,1)
"Max Chunk MB",fs.cnt "Free Extents"
from  dba_data_files df left outer join (select tablespace_name,file_id,sum(bytes) sum_bytes,max(bytes)
max_bytes,count(*) cnt
from dba_free_space group by tablespace_name,file_id) fs on (df.tablespace_name=fs.tablespace_name and df.file_id=fs.file_id) where df.tablespace_name=upper('&1');
set verify on