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

Author: SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator. View all posts by SandeepSingh