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 daily 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
col "Day" format a15
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') ,'DD-MON-YYYY') "Day"
from   v$loghist
group by to_date(to_char(first_time,'DD-MON-YYYY') ,'DD-MON-YYYY')
order by 2
/

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

No comments: