常用sql语句-归档日志生成情况

--查看生成归档日志个数

SELECT 
      trunc(completion_time) "Date",
      to_char(completion_time, 'Dy') "Day",
      THREAD#,
      dest_id,
      count(1) "Total",
      SUM(decode(to_char(completion_time, 'hh24'),'00',1,0)) "h0_1",
      SUM(decode(to_char(completion_time, 'hh24'),'01',1,0)) "h1_2",
      SUM(decode(to_char(completion_time, 'hh24'),'02',1,0)) "h2_3",
      SUM(decode(to_char(completion_time, 'hh24'),'03',1,0)) "h3_4",
      SUM(decode(to_char(completion_time, 'hh24'),'04',1,0)) "h4_5",
      SUM(decode(to_char(completion_time, 'hh24'),'05',1,0)) "h5_6",
      SUM(decode(to_char(completion_time, 'hh24'),'06',1,0)) "h6_7",
      SUM(decode(to_char(completion_time, 'hh24'),'07',1,0)) "h7_8",
      SUM(decode(to_char(completion_time, 'hh24'),'08',1,0)) "h8_9",
      SUM(decode(to_char(completion_time, 'hh24'),'09',1,0)) "h9_10",
      SUM(decode(to_char(completion_time, 'hh24'),'10',1,0)) "h10_11",
      SUM(decode(to_char(completion_time, 'hh24'),'11',1,0)) "h11_12",
      SUM(decode(to_char(completion_time, 'hh24'),'12',1,0)) "h12_13",
      SUM(decode(to_char(completion_time, 'hh24'),'13',1,0)) "h13_14",
      SUM(decode(to_char(completion_time, 'hh24'),'14',1,0)) "h14_15",
      SUM(decode(to_char(completion_time, 'hh24'),'15',1,0)) "h15_16",
      SUM(decode(to_char(completion_time, 'hh24'),'16',1,0)) "h16_17",
      SUM(decode(to_char(completion_time, 'hh24'),'17',1,0)) "h17_18",
      SUM(decode(to_char(completion_time, 'hh24'),'18',1,0)) "h18_19",
      SUM(decode(to_char(completion_time, 'hh24'),'19',1,0)) "h19_20",
      SUM(decode(to_char(completion_time, 'hh24'),'20',1,0)) "h20_21",
      SUM(decode(to_char(completion_time, 'hh24'),'21',1,0)) "h21_22",
      SUM(decode(to_char(completion_time, 'hh24'),'22',1,0)) "h22_23",
      SUM(decode(to_char(completion_time, 'hh24'),'23',1,0)) "h23_24"
FROM v$archived_log
group by trunc(completion_time), 
         to_char(completion_time, 'Dy'),
         THREAD#,
         dest_id
Order by 1 desc,3,4 
; 

--查看生成归档日志大小

SELECT 
      trunc(completion_time) "Date",
      to_char(completion_time, 'Dy') "Day",
      THREAD#,
      dest_id,
      round(sum(blocks*block_size)/1024/1024) "Total_mb",
      round(SUM(decode(to_char(completion_time, 'hh24'),'00',blocks*block_size,0))/1024/1024) h0_1_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'01',blocks*block_size,0))/1024/1024) h1_2_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'02',blocks*block_size,0))/1024/1024) h2_3_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'03',blocks*block_size,0))/1024/1024) h3_4_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'04',blocks*block_size,0))/1024/1024) h4_5_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'05',blocks*block_size,0))/1024/1024) h5_6_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'06',blocks*block_size,0))/1024/1024) h6_7_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'07',blocks*block_size,0))/1024/1024) h7_8_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'08',blocks*block_size,0))/1024/1024) h8_9_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'09',blocks*block_size,0))/1024/1024) h9_10_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'10',blocks*block_size,0))/1024/1024) h10_11_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'11',blocks*block_size,0))/1024/1024) h11_12_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'12',blocks*block_size,0))/1024/1024) h12_13_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'13',blocks*block_size,0))/1024/1024) h13_14_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'14',blocks*block_size,0))/1024/1024) h14_15_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'15',blocks*block_size,0))/1024/1024) h15_16_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'16',blocks*block_size,0))/1024/1024) h16_17_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'17',blocks*block_size,0))/1024/1024) h17_18_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'18',blocks*block_size,0))/1024/1024) h18_19_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'19',blocks*block_size,0))/1024/1024) h19_20_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'20',blocks*block_size,0))/1024/1024) h20_21_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'21',blocks*block_size,0))/1024/1024) h21_22_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'22',blocks*block_size,0))/1024/1024) h22_23_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'23',blocks*block_size,0))/1024/1024) h23_24_mb
FROM v$archived_log
group by trunc(completion_time), 
         to_char(completion_time, 'Dy'),
         THREAD#,
         dest_id
Order by 1 desc,3,4
;