oracle 查询归档增长量
set linesize 200
set pagesize 100
column day format a15 heading 'Day'
column d_0 format a3 heading '00'
column d_1 format a3 heading '01'
column d_2 format a3 heading '02'
column d_3 format a3 heading '03'
column d_4 format a3 heading '04'
column d_5 format a3 heading '05'
column d_6 format a3 heading '06'
column d_7 format a3 heading '07'
column d_8 format a3 heading '08'
column d_9 format a3 heading '09'
column d_10 format a3 heading '10'
column d_11 format a3 heading '11'
column d_12 format a3 heading '12'
column d_13 format a3 heading '13'
column d_14 format a3 heading '14'
column d_15 format a3 heading '15'
column d_16 format a3 heading '16'
column d_17 format a3 heading '17'
column d_18 format a3 heading '18'
column d_19 format a3 heading '19'
column d_20 format a3 heading '20'
column d_21 format a3 heading '21'
column d_22 format a3 heading '22'
column d_23 format a3 heading '23'
select
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_8,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0))) d_23,
count(*) total
from
v$log_history where first_time> sysdate-60
group by
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15)
order by
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) desc;
----2
SELECT to_char(first_time,'yyyy-mm-dd') Dt,
to_char(first_time, 'Dy') dy,
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
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"
FROM V$log_history
group by to_char(first_time,'yyyy-mm-dd') ,
to_char(first_time, 'Dy')
order by 1;
***精确到分钟
select
substr(to_char(FIRST_TIME,'yyyy-mm-dd hh24'),1,15) hh,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'08',1,0))) d_8,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'23',1,0))) d_23,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'24',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'24',1,0))) d_24,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'25',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'25',1,0))) d_25,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'26',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'26',1,0))) d_26,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'27',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'27',1,0))) d_27,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'28',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'28',1,0))) d_28,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'29',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'29',1,0))) d_29,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'30',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'30',1,0))) d_30,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'31',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'31',1,0))) d_31,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'32',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'32',1,0))) d_32,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'33',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'33',1,0))) d_33,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'34',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'34',1,0))) d_34,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'35',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'35',1,0))) d_35,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'36',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'36',1,0))) d_36,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'37',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'37',1,0))) d_37,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'38',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'38',1,0))) d_38,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'39',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'39',1,0))) d_39,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'40',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'40',1,0))) d_40,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'41',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'41',1,0))) d_41,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'42',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'42',1,0))) d_42,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'43',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'43',1,0))) d_43,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'44',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'44',1,0))) d_44,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'45',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'45',1,0))) d_45,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'46',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'46',1,0))) d_46,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'47',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'47',1,0))) d_47,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'48',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'48',1,0))) d_48,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'49',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'49',1,0))) d_49,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'50',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'50',1,0))) d_50,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'51',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'51',1,0))) d_51,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'52',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'52',1,0))) d_52,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'53',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'53',1,0))) d_53,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'54',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'54',1,0))) d_54,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'55',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'55',1,0))) d_55,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'56',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'56',1,0))) d_56,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'57',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'57',1,0))) d_57,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'58',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'58',1,0))) d_58,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'59',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'59',1,0))) d_59,
decode(sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'60',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'mi'),1,2),'60',1,0))) d_60,
count(*) total
from
v$log_history where to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi') between '2022-02-13 00:00' and '2022-02-15 00:00'
group by
substr(to_char(FIRST_TIME,'yyyy-mm-dd hh24'),1,15)
order by
substr(to_char(FIRST_TIME,'yyyy-mm-dd hh24'),1,15) desc;
***同样的效果
select TO_CHAR(first_time, 'MM/DD/RR HH:MI'), count(sequence#)
from v$log_history
WHERE first_time >= to_char(sysdate - 2)
group by TO_CHAR(first_time, 'MM/DD/RR HH:MI')
order by TO_CHAR(first_time, 'MM/DD/RR HH:MI');
***不同视图
select TO_CHAR(first_time, 'MM/DD/RR HH:MI'), count(sequence#)
from v$archived_log
WHERE dest_id = 1
and thread# = 1
and first_time >= to_char(sysdate - 2)
group by TO_CHAR(first_time, 'MM/DD/RR HH:MI')
order by TO_CHAR(first_time, 'MM/DD/RR HH:MI');
