Archive generated per day or hour in Oracle database

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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.