*************************************************************************** * 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