– 创建归档信息收集表
drop table SPDB_ARCH_FILE;
create table SPDB_ARCH_FILE (arch_create_time varchar(13) not null, arch_size_gb number(8,4), arch_num number(5), constraint PK_SPDB_ARCH_FILE primary key(arch_create_time));
– 部署归档日志信息定期收集脚本（每小时01分执行）
create or replace procedure p_gather_archivelog_info
is
v_count number;
v_start_time datetime;
v_arch_create_time varchar;
v_arch_size_gb number;
v_arch_num number;
cursor c_arch_file is select top 24 to_char(create_time,‘yyyy-mm-dd hh24’) arch_create_time, round(sum(len)/1024.0/1024/1024,4) arch_size_gb, count(1) arch_num from v$arch_file where create_time < trunc(sysdate,‘hh’) group by to_char(create_time,‘yyyy-mm-dd hh24’) order by to_char(create_time,‘yyyy-mm-dd hh24’) desc;
begin
v_start_time := sysdate;
v_count := 0;
print (to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) || ’ Start execute …’);
open c_arch_file;
loop
fetch c_arch_file into v_arch_create_time, v_arch_size_gb, v_arch_num;
exit when c_arch_file%NOTFOUND;
delete from spdb_arch_file where arch_create_time = v_arch_create_time;
insert into spdb_arch_file (arch_create_time, arch_size_gb, arch_num) values (v_arch_create_time, v_arch_size_gb, v_arch_num);
commit;
v_count := v_count+1;
end loop;
close c_arch_file;
print (to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) || ’ Delete expired archivelog information before 100 days’);
delete from spdb_arch_file where arch_create_time < to_char(sysdate-100, ‘yyyy-mm-dd hh24’);
commit;
print (to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) || ’ Total running time(s): ’ || datediff(SECOND, v_start_time, sysdate));
print (to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) || ’ Total number of archivelog record: ’ || v_count);
print (to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) || ’ End execute’);
end;
/
set serveroutput on
call p_gather_archivelog_info;
select * from SPDB_ARCH_FILE order by arch_create_time;
call SP_DROP_JOB(‘JOB_GATHER_ARCHIVELOG_INFO’);
call SP_CREATE_JOB(‘JOB_GATHER_ARCHIVELOG_INFO’,1,0,’’,0,0,’’,0,’’);
call SP_JOB_CONFIG_START(‘JOB_GATHER_ARCHIVELOG_INFO’);
call SP_ADD_JOB_STEP(‘JOB_GATHER_ARCHIVELOG_INFO’, ‘STEP_GATHER_ARCHIVELOG_INFO’, 0, ‘call p_gather_archivelog_info;’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_GATHER_ARCHIVELOG_INFO’, ‘SCH_GATHER_ARCHIVELOG_INFO’, 1, 1, 1, 0, 60, ‘00:01:00’, ‘23:59:59’, trunc(now), NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_GATHER_ARCHIVELOG_INFO’);