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"
       where trunc(first_time)>=trunc(sysdate -10 )
      GROUP BY trunc(first_time)
      order by  trunc(first_time);

1 thought on “Archive generated per day or hour in Oracle database

  1. Pingback: Setting Up Distcc: A Comprehensive Guide To Simplifying The Compilation Process – Programming & Design

Leave a Reply

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

You are commenting using your 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.