select thread#,
day,
sum(Day_G),
sum(HH00),
sum(HH01),
sum(HH02),
sum(HH03),
sum(HH04),
sum(HH05),
sum(HH06),
sum(HH07),
sum(HH08),
sum(HH09),
sum(HH10),
sum(HH11),
sum(HH12),
sum(HH13),
sum(HH14),
sum(HH15),
sum(HH16),
sum(HH17),
sum(HH18),
sum(HH19),
sum(HH20),
sum(HH21),
sum(HH22),
sum(HH23)
from (select thread#,
trunc(completion_time) Day,
round(blocks * block_size / 1024 / 1024 / 1024, 2) Day_G,
case
when (to_char(completion_time, 'HH24') = '00') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH00",
case
when (to_char(completion_time, 'HH24') = '01') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH01",
case
when (to_char(completion_time, 'HH24') = '02') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH02",
case
when (to_char(completion_time, 'HH24') = '03') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH03",
case
when (to_char(completion_time, 'HH24') = '04') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH04",
case
when (to_char(completion_time, 'HH24') = '05') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH05",
case
when (to_char(completion_time, 'HH24') = '06') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH06",
case
when (to_char(completion_time, 'HH24') = '07') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH07",
case
when (to_char(completion_time, 'HH24') = '08') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH08",
case
when (to_char(completion_time, 'HH24') = '09') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH09",
case
when (to_char(completion_time, 'HH24') = '10') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH10",
case
when (to_char(completion_time, 'HH24') = '11') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH11",
case
when (to_char(completion_time, 'HH24') = '12') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH12",
case
when (to_char(completion_time, 'HH24') = '13') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH13",
case
when (to_char(completion_time, 'HH24') = '14') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH14",
case
when (to_char(completion_time, 'HH24') = '15') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH15",
case
when (to_char(completion_time, 'HH24') = '16') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH16",
case
when (to_char(completion_time, 'HH24') = '17') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH17",
case
when (to_char(completion_time, 'HH24') = '18') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH18",
case
when (to_char(completion_time, 'HH24') = '19') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH19",
case
when (to_char(completion_time, 'HH24') = '20') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH20",
case
when (to_char(completion_time, 'HH24') = '21') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH21",
case
when (to_char(completion_time, 'HH24') = '22') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH22",
case
when (to_char(completion_time, 'HH24') = '23') then
round(blocks * block_size / 1024 / 1024 / 1024, 2)
else
0
end "HH23"
from v$archived_log)
group by thread#, day
order by 2, 1