***************************************************************************
* Description: SQL for querying archived log generated size by hour or day
* Compatiablity: RDBMS 11g, 12c
* Date: 17:37 PM EST, 01/04/2017
***************************************************************************


<1> Archived log size by day:
     |
     |__ SQL> select to_char(completion_time,'YYYYMMDD') as "DATE" ,sum(block_size*blocks)/1024/1024/1024 as "BYTES in GB"
              from v$archived_log group by to_char(completion_time,'YYYYMMDD') order by 1;
			  

                     DATE     BYTES in GB
                     -------- -----------
                     20161229  .591142654
                     20161230  .379516602
                     20161231  .159517765
                     20170101  .162820816
                     20170102  .169944286
                     20170103  .547352314
                     20170104  .209871769

					 
==================================================	
	
		
<2> Archived log size by hour:
     |
     |__ SQL> select to_char(completion_time,'YYYYMMDDHH24') as "DATE" ,sum(block_size*blocks)/1024/1024/1024 as "BYTES in GB"
              from v$archived_log
              where to_char(completion_time,'YYYYMMDD') = '20170102'
              group by to_char(completion_time,'YYYYMMDDHH24')
              order by 1;
			  
	 
                     DATE       BYTES in GB
                     ---------- -----------
                     2017010205  .046170712
                     2017010213  .031970024
                     2017010217  .009353161
                     2017010218  .050714016
                     2017010220  .031736374

	
	

Your Comments