Home A - Z
Menu

Blame

2141cb feagor 2025-12-06 08:58:05
1 
## Archivelog_Объем_сгенерированных_логов
2
3
```sql
4
select d.type
5
, trunc(completion_time) as "DATE"
6
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
7
, count(1) cnt
8
from v$archived_log l join V$ARCHIVE_DEST_STATUS d on d.DEST_ID = l.DEST_ID and d.status='VALID'
9
where first_time > trunc(sysdate-10)
10
group by d.type, trunc(completion_time)
11
order by 2 desc ,1;
12
```
13
#### Разбивка по часам
14
```ora
15
alter session set nls_date_format='DD MON YYYY';
16
select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total",
17
sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00",
18
sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01",
19
sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02",
20
sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03",
21
sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04",
22
sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05",
23
sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06",
24
sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07",
25
sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08",
26
sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09",
27
sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10",
28
sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11",
29
sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12",
30
sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13",
31
sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14",
32
sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15",
33
sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16",
34
sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17",
35
sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18",
36
sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19",
37
sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20",
38
sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21",
39
sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22",
40
sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23"
41
from
42
v$archived_log
43
where first_time > trunc(sysdate-10)
44
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
45
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;
46
```