Je l’oublie toujours et pourtant je l’utilise aussi souvent que possible : Le script qui donne le nombre de logs par heures …
SET VERIFY OFF
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
SELECT
TO_CHAR(first_time, 'MM/DD') DAY
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) H00
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) H01
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) H02
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) H03
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) H04
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) H05
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) H06
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) H07
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) H08
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) H09
, 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
, COUNT(*) TOTAL
FROM
v$log_history a
GROUP BY TO_CHAR(first_time, 'MM/DD')
/
Ce qui donne le fameux :
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Total
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --------
08/14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 12 16
08/15 12 13 14 13 9 14 13 12 10 15 9 14 15 7 17 5 17 7 15 8 16 4 19 5 283
08/16 15 8 6 17 5 17 9 11 11 18 12 2 17 15 10 3 14 20 2 13 0 20 17 1 263
08/17 11 0 20 19 3 10 1 21 18 2 12 0 12 18 13 4 11 0 16 23 8 0 0 0 222
08/28 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1
09/09 0 0 0 0 0 0 0 0 0 0 4 1 0 0 36 15 2 0 0 0 0 0 13 13 84
09/10 2 0 16 2 16 11 1 0 17 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 69
10/16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 25 2 8 11 50
10/17 6 0 5 8 4 6 10 1 0 1 2 1 2 13 12 0 1 6 7 7 1 0 2 2 97
10/18 3 3 3 5 18 2 4 7 5 9 1 2 2 3 3 3 6 1 18 1 0 0 0 0 99
10/23 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
10/27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1
10/28 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 11 10 0 0 0 0 24
10/29 0 0 0 0 0 0 0 0 0 4 18 7 0 3 5 5 6 6 4 3 3 5 4 15 88
10/30 1 1 5 5 6 3 4 7 3 3 2 8 0 17 0 0 0 0 0 0 0 0 0 0 65
Bien pratique pour tailler les logs