oracle查询备份表,oracle RMAN备份相关视图查询

对于oracle rman备份，如果备份的时候没有日志，或者在乙方给客户巡检的时候。可以通过视图v$rman_status、v$rman_backup_job_details查询。以下2条sql是我昨天下午大概看了下oracle v$rman开头的视图挑出的2个视图做的一个关联查询sql和单表查询sql，在生产上实践之后，觉得很实用。特意分享给大家！还有就是我要写博客，这是第一个帖子。嘿嘿！以下是sql文本：

--查看近7天备份情况，非常详细。ps：命令列耗费的时间不需要计算

select s.status as "备份状态",

b.INPUT_TYPE as "备份类型",

to_char(b.START_TIME,'yyyy-mm-dd hh24:mi:ss') as 总的开始时间,

to_char(b.end_time, 'yyyy-mm-dd hh24:mi:ss') as 总的结束时间,

trunc(b.ELAPSED_SECONDS/60,0) as 耗时多少分钟,

b.INPUT_BYTES_PER_SEC_DISPLAY "in_sec/s",

b.OUTPUT_BYTES_PER_SEC_DISPLAY "out_sec/s",

trunc((s.END_TIME-s.START_TIME)*24*60,0) "单个文件备份用时(分)",

to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') as "开始备份时间",

to_char(s.END_TIME, 'yyyy-mm-dd hh24:mi:ss') as "结束备份时间",

s.OPERATION as "命令",

trunc(s.INPUT_BYTES/1024/1024,2) as "INPUT-M",

trunc(s.OUTPUT_BYTES/1024/1024,2) as "OUTPUT-M",

s.OBJECT_TYPE as "对象类型",

s.MBYTES_PROCESSED as "百分比",

s.OUTPUT_DEVICE_TYPE as "设备类型"

from v$rman_status s,v$rman_backup_job_details b

where to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') < to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

and to_char(s.END_TIME, 'yyyy-mm-dd hh24:mi:ss') > to_char(sysdate-7,'yyyy-mm-dd hh24:mi:ss')

and s.COMMAND_ID=b.COMMAND_ID

order by  s.START_TIME desc ;

--查看近7天，历史备份和实时备份详细状态，很有意思。

select s.status as 备份状态,

trunc((s.END_TIME-s.START_TIME)*24*60,0) "备份用时(分钟)",

to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') as 开始备份时间,

to_char(s.END_TIME, 'yyyy-mm-dd hh24:mi:ss') as 结束备份时间,

s.OPERATION as 命令,

trunc(s.INPUT_BYTES/1024/1024,2) as "INPUT/M",

trunc(s.OUTPUT_BYTES/1024/1024,2) as "OUTPUT/M",

s.OBJECT_TYPE as "对象类型",

s.MBYTES_PROCESSED as 百分比,

s.OUTPUT_DEVICE_TYPE as "设备类型"

from v$rman_status s

where to_char(s.START_TIME, 'yyyy-mm-dd hh24:mi:ss') < to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

and to_char(s.END_TIME, 'yyyy-mm-dd hh24:mi:ss') > to_char(sysdate-7,'yyyy-mm-dd hh24:mi:ss')

order by  s.START_TIME desc ;

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