rem ----------------------------------------------------------------------- rem Filename: archdist.sql rem Purpose: Tabular display of redo-log archiving history (logs/hour) rem - Can only run from sqlplus rem Author: Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- set pagesize 50000 set veri off set colsep "" set termout off def time="time" -- Oracle7 col time new_value time select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time from dual where &&_O_RELEASE like '8%' -- Oracle8 / set termout on select substr(&&time, 1, 5) day, to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00", to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01", to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02", to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03", to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04", to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05", to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06", to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07", to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08", to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09", to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10", to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11", to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12", to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13", to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14", to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15", to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16", to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17", to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18", to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19", to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20", to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21", to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22", to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23" from sys.v_$log_history group by substr(&&time,1,5) / set colsep " "