rem rem  File: filemap.sql rem 

rem  This script provides information on the location/path, file name, 

rem  size and physical I/Os (if applicable) of all the Oracle files 

rem  (database, control and redo log) in an Oracle/UNIX platform. 


set pages 999
 col path  format a50      heading 'Path' 

col fname format a15     heading 'File Name' 
col fsize format 9999b   heading 'M bytes' 

col pr    format 999999b heading 'Phy. Reads' 

col pw    format 999999b heading 'Phy. Writes' 

col file_name form a20
break on path skip 1 
select substr(name,1,instr(name, '/', -1)-1 ) path,        
  substr(name,instr(name, '/', -1)+1 )  fname,   
  bytes/1048576  fsize,        phyrds pr,         phywrts pw 
from   v$datafile df, v$filestat fs  where df.file# = fs.file#
UNION 
select substr(name,1,instr(name, '/', -1)-1 ) path, 
       substr(name,instr(name, '/', -1)+1 ) fname, 0 fsize, 0 pr, 
      0 pw   
from   v$controlfile 
UNION 
select substr(lgf.member,1,instr(lgf.member,'/', -1)-1) path, 
       substr(lgf.member,instr(lgf.member, '/', -1)+1 ) fname,      
  lg.bytes/1048576 fsize, 0 pr, 0 pw 
from   v$logfile lgf, v$log lg  
where  lgf.group# = lg.group# 
order by 1,2  
/