The below script
will provide you the daily archive log generation in GB.
Column date format a20
Column "Size in GB" format 50
select to_char(completion_time, 'DD-MON-YY') "Date", sum(blocks*block_size/(1024*1024/1024)) "Size in GB", count(*) " No of Archives"
from v$archived_log
group by to_char(completion_time, 'DD-MON-YY')
order by to_date(to_char(completion_time, 'DD-MON-YY'));
For getting the
hourly generation of archivelogs, the below mentioned script will be helpful.
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%'
or &&_O_RELEASE like '9%' -- Oracle8
-- for
10g where &&_O_RELEASE like '8%'
or &&_O_RELEASE like '9%' -- Oracle8
/
Column
"day" format a20
column
" 00" format a4
column
" 01" format a4
column
" 02" format a4
column
" 03" format a4
column
" 04" format a4
column
" 05" format a4
column
" 06" format a4
column
" 07" format a4
column
" 08" format a4
column
" 09" format a4
column
" 10" format a4
column
" 11" format a4
column
" 12" format a4
column
" 13" format a4
column
" 14" format a4
column
" 15" format a4
column
" 16" format a4
column
" 17" format a4
column
" 18" format a4
column
" 19" format a4
column
" 20" format a4
column
" 21" format a4
column
" 22" format a4
column
" 23" format a4
set
termout on
select
substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'999') "
00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'999') "
01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'999') "
02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'999') "
03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'999') "
04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'999') "
05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'999') "
06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'999') "
07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'999') "
08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'999') "
09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'999') "
10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'999') "
11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'999') "
12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'999') "
13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'999') "
14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'999') "
15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'999') "
16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'999') "
17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'999') "
18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'999') "
19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'999') "
20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'999') "
21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'999') "
22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'999') "
23"
from sys.v_$log_history
where
substr(&&time,4,2)=substr(to_char(sysdate,'dd/mm/yy'),4,2)
group by substr(&&time,1,5)
/
Not all
the contents are defined/ derived by myself.
I have
consolidated/added accordingly for ease of access.
No comments:
Post a Comment