set linesize 200 pagesize 1000
column day format a3
column total format 9999
column hr00 format 999
column hr01 format 999
column hr02 format 999
column hr03 format 999
column hr04 format 999
column hr04 format 999
column hr05 format 999
column hr06 format 999
column hr07 format 999
column hr08 format 999
column hr09 format 999
column hr10 format 999
column hr11 format 999
column hr12 format 999
column hr13 format 999
column hr14 format 999
column hr15 format 999
column hr16 format 999
column hr17 format 999
column hr18 format 999
column hr19 format 999
column hr20 format 999
column hr21 format 999
column hr22 format 999
column hr23 format 999
column hr24 format 999
break on report
compute max of "total" on report
compute max of "hr00" on report
compute max of "hr01" on report
compute max of "hr02" on report
compute max of "hr03" on report
compute max of "hr04" on report
compute max of "hr05" on report
compute max of "hr06" on report
compute max of "hr07" on report
compute max of "hr08" on report
compute max of "hr09" on report
compute max of "hr10" on report
compute max of "hr11" on report
compute max of "hr12" on report
compute max of "hr13" on report
compute max of "hr14" on report
compute max of "hr15" on report
compute max of "hr16" on report
compute max of "hr17" on report
compute max of "hr18" on report
compute max of "hr19" on report
compute max of "hr20" on report
compute max of "hr21" on report
compute max of "hr22" on report
compute max of "hr23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report
alter session set nls_date_format='DD MON YYYY';
select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total",
sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "hr00",
sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "hr01",
sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "hr02",
sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "hr03",
sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "hr04",
sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "hr05",
sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "hr06",
sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "hr07",
sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "hr08",
sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "hr09",
sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "hr10",
sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "hr11",
sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "hr12",
sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "hr13",
sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "hr14",
sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "hr15",
sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "hr16",
sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "hr17",
sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "hr18",
sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "hr19",
sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "hr20",
sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "hr21",
sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "hr22",
sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "hr23"
from
v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;
REM Script to calculate the archive log size generated per day for each Instances.
select THREAD#, trunc(completion_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
, trunc(sum(blocks*block_size)/1024/1024) as MB
, sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time)
order by 2,1;
