--------------------------------------------------------------------------
--File: checkfiles.sql
--Function: check all files including datafile,controlfile,logfile
--Author: NinGoo
--Create: 2006-3-11
--Modify:
--Version: 1.0
--------------------------------------------------------------------------

set echo off
set feedback off
set verify off
set timing off
set linesize 80
set pagesize 200

column name for a44
column status for a19
column size(M) for a7
column error for a20
ttitle center 'The infomation of all files' skip 3
--------------------------------------------------------------------------
select 'Data' as "TYPE",to_char(file#)||':'||name as "NAME",
status || '(' || enabled || ')' as Status,
to_char(bytes/1024/1024) as "SIZE(M)"
from v$datafile
union all
select LPAD('-',7,'-'),LPAD('-',44,'-'),LPAD('-',19,'-'),LPAD('-',7,'-') from dual
union all
select 'Temp',to_char(file#)||':'||name as "name",status || '(' || enabled || ')',
to_char(bytes/1024/1024)
from v$tempfile
union all
select LPAD('-',7,'-'),LPAD('-',44,'-'),LPAD('-',19,'-'),LPAD('-',7,'-') from dual
union all
select 'Redo',to_char(l.group#)||':'||lf.MEMBER,
lf.type || '(seq:' || to_char(l.sequence#)||')'||decode(l.archived,'YES',' A'),
to_char(l.BYTES/1024/1024 )
from v$logfile lf,v$log l
where lf.group#=l.group#
union all
select LPAD('-',7,'-'),LPAD('-',44,'-'),LPAD('-',19,'-'),LPAD('-',7,'-') from dual
union all
select 'Control',name,status ,
to_char(FILE_SIZE_BLKS*BLOCK_SIZE/1024/1024)
from v$controlfile
union all
select LPAD('-',7,'-'),LPAD('-',44,'-'),LPAD('-',19,'-'),LPAD('-',7,'-') from dual;

ttitle center 'the files which should be recoverd' skip 3
select a.file#,a.online_status as "STAT",a.error,b.name
from v$recover_file a, v$datafile b
where a.file#=b.file#;
--------------------------------------------------------------------------
set feedback on
set verify on
ttitle off