oracle 归档日志 sql,ORACLE 归档日志信息sql

PROMPT

PROMPT redolog switch

set linesize 300

set pages 100

column d1 form a20 heading "Date"

column sw_cnt form 99999 heading 'Number|of|Switches'

column Mb form 999,999 heading "Redo Size"

column redoMbytes form 999,999,9999 heading "Redo Log File Size (Mb)"

break on report

compute sum of sw_cnt on report

compute sum of Mb on report

var redoMbytes number;

begin

select max(bytes)/1024/1024 into :redoMbytes from v$log;

end;

/

print redoMbytes

select trunc(first_time) d1

, count(*) sw_cnt

, count(*) * :redoMbytes Mb

from v$log_history

group by trunc(first_time)

/

最近三个月日志归档频繁度

select to_char(first_time,'yyyy-mm-dd'),thread#,count(*)

from v$log_history

where first_time>sysdate-90

group by to_char(first_time,'yyyy-mm-dd'),thread#

having count(*)>20

order by thread#

/

select a.f_time "日期",

a.thread#,

ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 1024) "每天归档量(G)",

ceil(sum(a.blocks * a.block_size) / 1024 / 1024 / 24) "每小时avg归档量(M)"

from (select distinct sequence#,

thread#,

blocks,

block_size,

to_char(first_time, 'yyyy/mm/dd') f_time

from v$archived_log) a

group by a.f_time, a.thread#

order by 3 desc;

日期  THREAD# 每天归档量(G) 每小时avg归档量(M)

---------- ---------- ------------- ------------------

2011/10/26  1  10  402

2011/10/31  1  5  175

2011/10/21  1  5  179

2011/10/27  1  5  171

2011/11/01  1  5  179

2011/10/18  1  5  173

2011/10/19  1  5  176

2011/10/20  1  5  185

2011/10/23  1  4  149

2011/10/29  1  4  147

2011/10/17  1  4  168

日期  THREAD# 每天归档量(G) 每小时avg归档量(M)

---------- ---------- ------------- ------------------

2011/10/28  1  4  129

2011/10/16  1  4  141

2011/10/24  1  4  168

2011/10/25  1  4  169

2011/10/30  1  4  132

2011/10/22  1  2  84

2011/11/02  1  1  8

2011/10/15  1  1  21

select to_char(first_time,'yyyy/mm/dd:hh24') "日期",thread#,count(1) "高峰时每小时归档个数"

from v$log_history

where trunc(first_time)

in (select d_time

from (select max(count(1)) m_arch

from v$log_history group by trunc(first_time)) a,

(select trunc(first_time) d_time,count(1) d_arch

from v$log_history group by trunc(first_time)) b

where a.m_arch=b.d_arch)

group by to_char(first_time,'yyyy/mm/dd:hh24'),thread# order by 3 desc ,thread#,1 ;

日期  THREAD# 高峰时每小时归档个数

------------- ---------- --------------------

2011/10/31:18  1  21

2011/10/31:17  1  7

2011/10/31:10  1  3

2011/10/31:04  1  2

2011/10/31:00  1  1

2011/10/31:09  1  1

2011/10/31:13  1  1

2011/10/31:16  1  1