set linesize 100
set pagesize 9999
set feedback off
set verify   off
set echo     off
set pause    off
clear        columns
clear        breaks

ttitle skip 1 -
       left 'All database files'                     skip -
       left '=================='                     skip -
       left 'Control, data, redo and dump_files'     skip 2

column file_type heading 'Type'      format a20
column name      heading 'Name'      format a48
column status    heading 'Status'    format a8
column grp       heading 'Grp'       format 999
column mbytes    heading 'Size (Mb)' format 99,990.9

break on file_type on grp

select 'Data'                  file_type
,      to_number(null)         grp
,      name                    name
,      status                  status
,      bytes / (1024 * 1024)   mbytes
from   v$datafile
where  name is not null
UNION
select 'Redo'                  file_type
,      lf.group#               grp
,      lf.member               name
,      l.status                status
,      l.bytes / (1024 * 1024) mbytes
from   v$log      l
,      v$logfile   lf
where  l.group# = lf.group#
UNION
select 'Control'               file_type
,      to_number(null)         grp
,      name                    name
,      status                  status
,      to_number(null)         mbytes
from   v$controlfile
UNION
select name                    file_type
,      to_number(null)         grp
,      value                   name
,      to_char(null)           status
,      to_number(null)         mbytes
from   v$parameter
where  name like '%dest%'      and
       isdefault = 'FALSE'
/