SELECT TO_CHAR(first_time,'MM/DD') DAY             ,
TO_CHAR(first_time,'YYYY/MM/DD') DAY2             ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) H00 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) H01 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) H02 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) H03 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) H04 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) H05 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) H06 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) H07 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) H08 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) H09 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'10',1,0)) H10 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'11',1,0)) H11 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'12',1,0)) H12 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'13',1,0)) H13 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'14',1,0)) H14 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'15',1,0)) H15 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'16',1,0)) H16 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'17',1,0)) H17 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'18',1,0)) H18 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'19',1,0)) H19 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'20',1,0)) H20 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'21',1,0)) H21 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'22',1,0)) H22 ,
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'23',1,0)) H23 ,
COUNT(*)
||'('||trim(to_char(sum(blocks*block_size)/1024/1024,'99,999.9'))||'M)'    TOTAL
FROM
(select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time
from
v$archived_log  a
where COMPLETION_TIME > sysdate - &day
and dest_id = 1
group by sequence#
)
group by  TO_CHAR(first_time,'MM/DD'), TO_CHAR(first_time,'YYYY/MM/DD')
order by TO_CHAR(first_time,'YYYY/MM/DD') desc

Related posts:

1. undo相关的sql
2. 表空间使用状况查询
3. 等待事件相关的SQL
4. sqlplus下如何看执行计划
5. 一道有意思的sql题