##  DBA Command Post - files.sql


col name  format a60 heading "Control Files"

select name                       
from   sys.v_$controlfile
/

select name, value
from   sys.v_$parameter
where  name like '%archive_dest%'
or     name like '%dump_dest%'
/

col Grp    format 9999
col member format a50 heading "Online REDO Logs"
col File#  format 9999
col name   format a50 heading "Online REDO Logs"
break on Grp

select *
from   sys.v_$logfile
/

col sequence#    format 99999  heading "Seqn"
col archive_name format a55    heading "Archived REDO Logs"
col time         format a17 

select sequence#, archive_name, time
from   sys.v_$log_history
where  rownum < 10
/

col Tspace    format a15
col status    format a3  heading Sta
col Id        format 99
col Mbyte     format 9999
col name      format a50 heading "Database Data Files"
col Reads     format 99,999,999
col Writes    format 99,999,999

break on report
compute sum of Mbyte on report

select F.file_id Id, 
       F.file_name name, 
       F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status, 
       F.tablespace_name Tspace
from   sys.dba_data_files F
/