ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 获得datafile,control file,redo logfile的信息

获得datafile,control file,redo logfile的信息

原创 Linux操作系统 作者:NinGoo 时间:2019-06-02 19:33:04 0 删除 编辑

--------------------------------------------------------------------------
--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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50210/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    131832