Ameer Khan

Using this SQL Query we can find out number archive log generated per hour in last 30 days..

I used in where clause  “sysdate – 30” to find out the result of last 30 days. You can change the number of days as per your requirement. 

set echo off;

set pages 10000

set sqlbl on;

col day for a12

set lines 1000

set pages 999

col “00” for a3

col “01” for a3

col “02” for a3

col “03” for a3

col “04” for a3

col “05” for a3

col “06” for a3

col “07” for a3

col “08” for a3

col “09” for a3

col “10” for a3

col “11” for a3

col “12” for a3

col “13” for a3

col “14” for a3

col “15” for a3

col “16” for a4

col “17” for a3

col “18” for a4

col “19” for a3

col “20” for a3

col “21” for a3

col “22” for a3

col “23” for a3

SELECT

to_char(first_time,’DD-MON-YYYY’) day,

to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’9999′) “00”,

to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’9999′) “01”,

to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’9999′) “02”,

to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’9999′) “03”,

to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’9999′) “04”,

to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’9999′) “05”,

to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’9999′) “06”,

to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’9999′) “07”,

to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’9999′) “08”,

to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’9999′) “09”,

to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’9999′) “10”,

to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’9999′) “11”,

to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’9999′) “12”,

to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’9999′) “13”,

to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’9999′) “14”,

to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’9999′) “15”,

to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’9999′) “16”,

to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’9999′) “17”,

to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’9999′) “18”,

to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’9999′) “19”,

to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’9999′) “20”,

to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’9999′) “21”,

to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’9999′) “22”,

to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’9999′) “23”,

count(*) Total

from

gv$log_history

WHERE first_time > sysdate -7

GROUP by

to_char(first_time,’DD-MON-YYYY’),trunc(first_time) order by trunc(first_time);

If there is a standby database configured, using the below query it would show the total number of archivelog generation for both primary and standby locations.

set lines 300

col 00 format A5

col 01 format A5

col 02 format A5

col 03 format A5

col 04 format A5

col 05 format A5

col 06 format A5

col 07 format A5

col 08 format A5

col 09 format A5

col 10 format A5

col 11 format A5

col 12 format A5

col 13 format A5

col 14 format A5

col 15 format A5

col 16 format A5

col 17 format A5

col 18 format A5

col 19 format A5

col 20 format A5

col 21 format A5

col 22 format A5

col 23 format A5

SELECT to_date(first_time) DAY,

to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’9999′) “00 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’9999′) “01 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’9999′) “02 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’9999′) “03 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’9999′) “04 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’9999′) “05 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’9999′) “06 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’9999′) “07 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’9999′) “08 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’9999′) “09 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’9999′) “10 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’9999′) “11 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’9999′) “12 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’9999′) “13 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’9999′) “14 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’9999′) “15 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’9999′) “16 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’9999′) “17 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’9999′) “18 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’9999′) “19 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’9999′) “20 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’9999′) “21 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’9999′) “22 Hr”,

to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’9999′) “23 Hr”

from

v$archived_log

where to_date(first_time) > sysdate – 30

GROUP by

to_char(first_time,’YYYY-MON-DD’), to_date(first_time)

order by to_date(first_time);

The query output looks like this