概述

平时工作中如果要去扩容归档空间之类，有些人总会问你每天的归档产生大概多少，这里我分享两个工作中比较常用的脚本，大家以后就可以对数据库的归档情况知根知底啦！

-- 查看每天产生归档日志的数据量

alter session set nls_date_format=‘yyyy.mm.dd hh24:mi:ss‘;

select trunc(completion_time) as ARC_DATE,

count(*) as COUNT,

round((sum(blocks * block_size) / 1024 / 1024), 2) as ARC_MB

from v$archived_log

group by trunc(completion_time)

order by trunc(completion_time);

这里我归档是按每天60G来算。

-- 查看最近几天，每小时归档日志产生数量

SELECT SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS‘),1,5) Day,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘00‘,1,0)) H00,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘01‘,1,0)) H01,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘02‘,1,0)) H02,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘03‘,1,0)) H03,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘04‘,1,0)) H04,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘05‘,1,0)) H05,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘06‘,1,0)) H06,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘07‘,1,0)) H07,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘08‘,1,0)) H08,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘09‘,1,0)) H09,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘10‘,1,0)) H10,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘11‘,1,0)) H11,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘12‘,1,0)) H12,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘13‘,1,0)) H13,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘14‘,1,0)) H14,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘15‘,1,0)) H15,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘16‘,1,0)) H16,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘17‘,1,0)) H17,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘18‘,1,0)) H18,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘19‘,1,0)) H19,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘20‘,1,0)) H20,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘21‘,1,0)) H21,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘22‘,1,0)) H22,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS‘),10,2),‘23‘,1,0)) H23,
 COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS‘),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS‘),1,5) DESC;

纯干货！觉得这脚本有用、有帮助的朋友多多点赞转发哦！

后面会分享更多DBA实用的内容，感兴趣的朋友可以关注下~