Commit 2141cb
2025-12-06 08:58:05 feagor: -/-| /dev/null .. oracle/oracle_files/Archivelog_Объем_сгенерированных_логов.md | |
| @@ 0,0 1,46 @@ | |
| + | ## Archivelog_Объем_сгенерированных_логов |
| + | |
| + | ```sql |
| + | select d.type |
| + | , trunc(completion_time) as "DATE" |
| + | , trunc(sum(blocks*block_size)/1024/1024/1024) as GB |
| + | , count(1) cnt |
| + | from v$archived_log l join V$ARCHIVE_DEST_STATUS d on d.DEST_ID = l.DEST_ID and d.status='VALID' |
| + | where first_time > trunc(sysdate-10) |
| + | group by d.type, trunc(completion_time) |
| + | order by 2 desc ,1; |
| + | ``` |
| + | #### Разбивка по часам |
| + | ```ora |
| + | alter session set nls_date_format='DD MON YYYY'; |
| + | select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total", |
| + | sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00", |
| + | sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01", |
| + | sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02", |
| + | sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03", |
| + | sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04", |
| + | sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05", |
| + | sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06", |
| + | sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07", |
| + | sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08", |
| + | sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09", |
| + | sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10", |
| + | sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11", |
| + | sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12", |
| + | sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13", |
| + | sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14", |
| + | sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15", |
| + | sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16", |
| + | sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17", |
| + | sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18", |
| + | sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19", |
| + | sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20", |
| + | sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21", |
| + | sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22", |
| + | sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23" |
| + | from |
| + | v$archived_log |
| + | where first_time > trunc(sysdate-10) |
| + | and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL') |
| + | group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1; |
| + | ``` |