DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Friday, July 31, 2015

How to check archivelog generation size on a hourly basis

alter session set nls_date_format='DD-MON-YYYY:HH24';

set pagesize 60
set linesize 90
set echo off;
set verify off;
set feedback off;
column sysdate  new_value headdate noprint;
column value    new_value dbname   noprint;
ttitle off
select sysdate,value from v$parameter
 where name = 'db_name';

ttitle skip 1 left "(redo_rate  )" -
       center "State Street Corp ITD/DBA" -
       right "page: " sql.pno " " -
skip 1 left   headdate   -
       center "Redo File Volumes by Day    " -
skip 1 left   "database: " dbname  " " -
       center dbname " " -
       right dbname " " -
skip 2;

ttitle on
spool redo_rate.&dbname
col log_size new_value logsize format 999,999,999,999 Heading "LogSize|KBytes";
col logsize format 999,999,999  
col "Total KBytes" format 999,999,999,999,999
rem col "Day" format a18
ttitle off
select distinct bytes/1024 log_size
from   v$log;
ttitle on;
select count(first_time)*&logsize "Total KBytes",
       to_date(to_char(first_time,'DD-MON-YYYY:HH24') ,'DD-MON-YYYY:HH24')
from   v$loghist
group by to_date(to_char(first_time,'DD-MON-YYYY:HH24') ,'DD-MON-YYYY:HH24')
order by 2

ttitle off
select distinct bytes/1024 log_size
from   v$log;

No comments: