Script for calculate archive generated in GB Size or Count in Oracle
Script for archive generate per day wise get Count and Size:
select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Script for archive generate per hours get result in count and size:
col hour for a20
select to_char(trunc(COMPLETION_TIME,'HH'),'dd-mm-yyyy HH24:MI:SS') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Archive Log generated count per hours in 24 hours format output for last 10 days:
set head on
col "Day" for a10
col "00" for a4
col "01" for a4
col "02" for a4
col "03" for a4
col "04" for a4
col "05" for a4
col "06" for a4
col "07" for a4
col "08" for a4
col "09" for a4
col "10" for a4
col "11" for a4
col "12" for a4
col "13" for a4
col "14" for a4
col "15" for a4
col "16" for a4
col "17" for a4
col "18" for a4
col "19" for a4
col "20" for a4
col "21" for a4
col "22" for a4
col "23" for a4
set lines 200 pages 999
set colsep ""
SELECT trunc(first_time) "Day",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
FROM V$LOG_HISTORY
where trunc(first_time)>=trunc(sysdate -10 )
GROUP BY trunc(first_time)
order by trunc(first_time);
Pingback: Setting Up Distcc: A Comprehensive Guide To Simplifying The Compilation Process – Programming & Design