Tip of the Week
Tip for Week of March 20, 2006

Log Switch Activity

This tip comes from Warren Nash, a Principle Developer at Capgemini UK plc, in Woking, UK.

The following tip will calculate both the number of log switches per day and the average per month for the last couple of months.

WHENEVER SQLERROR EXIT

PROMPT Running log_history.sql -- V1.0

SPOOL &1/log/log_history.out

WHENEVER SQLERROR CONTINUE

ttitle left 'Current Date - Time' skip 1 -
left '==========================' skip 2

select to_char(sysdate, 'DD-MM-YY HH24:MI') "Current Date/Time" from dual
/
ttitle off

ttitle left 'Log History Switching Stats (Last couple of months)' skip 1 -
left '===================================================' skip 2


set lines 132
set pages 100

col A format a10 heading "Month"
col B format a25 heading "Archive Date"
col C format 999 heading "Switches"

compute AVG of C on A
compute AVG of C on REPORT

break on A skip 1 on REPORT skip 1

select to_char(trunc(first_time), 'Month') A ,
to_char(trunc(first_time), 'Day : DD-Mon-YYYY') B ,
count(*) C
from v$log_history
where trunc(first_time) > last_day(sysdate-100) +1
group by trunc(first_time)
/

ttitle off

SPOOL off

REM exit