Oracle 시간별 로그스위치 횟수 조회하기

어제 포스팅한 로그스위치 횟수를 조회하는 쿼리문을 공유하도록 하겠습니다.

 

아래의 쿼리는2021년 1월 25일부터 2021년2월 3일까지의 내용을 조회한것으로

 

사용시 해당 부분을 조회하는 날로 수정하여 사용해야합니다.

 

set pagesize 120;
set linesize 2000;
col day for a8;

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)),'999') H00
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)),'999') H01
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)),'999') H02
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)),'999') H03
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)),'999') H04
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)),'999') H05
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)),'999') H06
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)),'999') H07
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)),'999') H08
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)),'999') H09
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)),'999') H10
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)),'999') H11
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)),'999') H12
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)),'999') H13
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)),'999') H14
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)),'999') H15
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)),'999') H16
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)),'999') H17
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)),'999') H18
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)),'999') H19
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)),'999') H20
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)),'999') H21
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)),'999') H22
  , TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)),'999') H23
  , COUNT(*)                                                                                TOTAL
FROM
  v$log_history  a
WHERE
    (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
    >=
    TO_DATE('20210125', 'YYYYMMDD HH24:MI:SS')
    )
    AND
    (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
    <=
    TO_DATE('20210203', 'YYYYMMDD HH24:MI:SS')
    )
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY DAY
/

조회결과는 다음과 같습니다.

DAY	 H00	  H01	   H02	    H03      H04      H05      H06	H07	 H08	  H09	   H10	    H11      H12      H13      H14	H15	 H16	  H17	   H18	    H19      H20      H21      H22	H23	      TOTAL
-------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- ----------
01/25	    2	     3	      3        3	3	 3	  5	   4	    6	     7	      8        8	7	13	  9	  11	   13	    16	     13        8	4	 6	  5	   3		163
01/26	    5	     4	      6        4	7	 5	  7	  10	    9	    10	     11       14	7	 8	 10	  13	   14	    16	     13        7	4	 7	  5	   4		200
01/27	    4	     5	      6        5	6	 4	  7	   7	   11	    14	     11        8	9	 9	  9	  12	   13	    15	     14        7	5	 7	  5	   4		197
01/28	    6	     4	      6        5	5	 5	  4	   9	   12	    12	      9       10	7	 8	  9	  11	   13	    14	     13        6	4	 7	  6	   3		188
01/29	    5	     4	      6        4	5	 5	  4	   8	    8	    15	     10        8	6	 8	  7	  12	   12	    13	     11        6	4	 6	  4	   4		175
01/30	    5	     3	      6        4	4	 5	  4	   6	    7	     8	      7        6	6	 4	  4	   3	   11	     3	      3        3	3	 3	  3	   3		114
01/31	    4	     2	      3        3	3	 2	  2	   4	    2	     2	      3        2	2	 2	  3	   3	    2	     3	      2        2	2	 3	  3	   2		 61
02/01	   11	     3	      2        3	2	 3	  5	   3	   14	     7	      9        7	6	 8	 23	  12	   14	    16	     13        8	4	 6	  5	   4		188
02/02	   13	     5	      4        5	5	 4	  8	   7	    8	    13	     10       10	6	11	 12	  15	   15	    18	     15        8	5	 3	  5	   4		209
02/03	   18	     8	      4        4	4	 6	  5	  10	    9	    11	     10       10	7	 8	 16	   9	    0	     0	      0        0	0	 0	  0	   0		139

10 rows selected.