Page tree
Skip to end of metadata
Go to start of metadata
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



  • No labels