--File Name: filestats.sql
--Purpose:   Report File Level Statistics

colum "name" format A18
column "#" format 99
colum "TS NAME" format A23
colum "MB" format 99999.99
select substr(a.file#,1,2) "#",
       substr(a.name,1,18) "name",
       substr(df.tablespace_name,1,23) "TS NAME",
       a.bytes/1024/1024 "MB",
       b.phyrds,
       b.phywrts
from v$datafile a,
     v$filestat b,
     dba_data_files df
where (a.file#=b.file#) AND
      (df.file_id=a.file#)
order by a.file#;

-- File Stats    -------------------------------------------------------------------------------------------------------
-- v$filestat: select file stats for each tablespace_name
select substr(b.tablespace_name,1,30) as tablespace, a.* 
from v$filestat a, dba_data_files b 
where a.file# = b.file_id
order by a.avgiotim;