一、基础环境准备（首次执行）
-- 1. 创建表空间监控表（存储使用率、容量等信息）
create table monitor_tablespace_rate (
tbs_name varchar2(50), -- 表空间名
total_gb number, -- 总容量(GB)
used_gb number, -- 已使用(GB)
free_gb number, -- 剩余空间(GB)
rate number, -- 使用率(%)
maxextend_gb number, -- 最大可扩展容量(GB)
last_check_time date default sysdate -- 最后检查时间
);
comment on table monitor_tablespace_rate is '表空间使用率监控表';
-- 2. 创建审计日志清理记录表（跟踪清理操作）
create table audit_clean_log (
clean_time date default sysdate, -- 清理时间
aud_size_before number, -- 清理前AUD$表大小(GB)
aud_size_after number, -- 清理后AUD$表大小(GB)
clean_result varchar2(100) -- 清理结果
);
comment on table audit_clean_log is 'AUD$表清理日志';
-- 3. 创建AWR快照清理记录表
create table awr_clean_log (
clean_time date default sysdate, -- 清理时间
dbid number, -- 数据库ID
min_snap_id number, -- 清理的最小快照ID
max_snap_id number, -- 清理的最大快照ID
clean_count number, -- 清理的快照数量
clean_result varchar2(100) -- 清理结果
);
comment on table awr_clean_log is 'AWR快照清理日志';
二、核心存储过程（自动扩容+清理）
-- 创建存储过程：自动扩容表空间+清理审计日志及AWR快照
create or replace procedure proc_tbs_auto_manage
as
-- 扩容相关变量
v_tbs_name varchar2(50);
v_rate number;
v_file_name varchar2(200);
v_new_file_name varchar2(200);
v_file_num number;
v_sql varchar2(500);
-- 清理相关变量
v_aud_size_before number; -- AUD$表清理前大小(GB)
v_aud_size_after number; -- 清理后大小(GB)
v_dbid number; -- 数据库ID
v_min_snap_id number; -- 最小快照ID
v_max_snap_id number; -- 最大快照ID
v_snap_count number; -- 快照数量
begin
-- --------------------------
-- 步骤1：更新表空间监控数据
-- --------------------------
truncate table monitor_tablespace_rate;
insert into monitor_tablespace_rate(tbs_name, total_gb, used_gb, free_gb, rate, maxextend_gb)
select
d.tablespace_name as tbs_name,
round(d.tot_gb, 2) as total_gb,
round(d.tot_gb - f.free_gb, 2) as used_gb,
round(f.free_gb, 2) as free_gb,
round((d.tot_gb - f.free_gb)/d.max_gb * 100, 2) as rate, -- 使用率(%)
round(d.max_gb, 2) as maxextend_gb
from (
-- 表空间总容量及最大可扩展容量
select
tablespace_name,
sum(bytes)/(1024*1024*1024) as tot_gb, -- 总容量(GB)
sum(decode(maxbytes, 0, bytes, maxbytes))/(1024*1024*1024) as max_gb -- 最大可扩展容量
from dba_data_files
group by tablespace_name
) d
left join (
-- 表空间剩余空间
select
tablespace_name,
sum(bytes)/(1024*1024*1024) as free_gb -- 剩余空间(GB)
from dba_free_space
group by tablespace_name
) f on d.tablespace_name = f.tablespace_name
where d.tablespace_name not like '%UNDO%' -- 排除UNDO表空间
and d.tablespace_name not like '%TEMP%'; -- 排除临时表空间
-- --------------------------
-- 步骤2：自动扩容表空间（使用率>85%时）
-- --------------------------
for tbs in (select tbs_name, rate from monitor_tablespace_rate where rate > 85) loop
v_tbs_name := tbs.tbs_name;
v_rate := tbs.rate;
-- 获取该表空间现有数据文件信息（用于生成新文件名）
select max(file_id), max(file_name)
into v_file_num, v_file_name
from dba_data_files
where tablespace_name = v_tbs_name;
-- 生成新数据文件路径（在原路径后加序号，如原路径/data/ts1.dbf→/data/ts1_2.dbf）
v_file_num := v_file_num + 1;
v_new_file_name := substr(v_file_name, 1, instr(v_file_name, '.', -1)) || v_file_num || '.dbf';
-- 执行扩容：新增1GB数据文件，开启自动扩展
v_sql := 'alter tablespace ' || v_tbs_name ||
' add datafile ''' || v_new_file_name || '''' ||
' size 1G autoextend on next 500M maxsize unlimited';
execute immediate v_sql;
-- 输出日志
dbms_output.put_line('['||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'] '||v_tbs_name||' 扩容成功，新增文件：'||v_new_file_name);
end loop;
-- --------------------------
-- 步骤3：清理SYSTEM表空间AUD$审计表（大小>5GB时）
-- --------------------------
-- 检查AUD$表当前大小(GB)
select nvl(sum(bytes)/(1024*1024*1024), 0) into v_aud_size_before
from dba_segments
where segment_name = 'AUD$' and owner = 'SYS';
if v_aud_size_before > 5 then -- 阈值：超过5GB则清理
-- 11g+推荐使用DBMS_AUDIT_MGMT清理（避免直接TRUNCATE）
begin
-- 初始化清理（若未初始化）
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
default_cleanup_interval => 24
);
-- 执行清理
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
use_last_arch_timestamp => false
);
-- 记录清理后大小
select nvl(sum(bytes)/(1024*1024*1024), 0) into v_aud_size_after
from dba_segments
where segment_name = 'AUD$' and owner = 'SYS';
-- 写入清理日志
insert into audit_clean_log(aud_size_before, aud_size_after, clean_result)
values(v_aud_size_before, v_aud_size_after, '清理成功');
dbms_output.put_line('['||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'] AUD$表清理完成，清理前：'||v_aud_size_before||'GB，清理后：'||v_aud_size_after||'GB');
exception
when others then
insert into audit_clean_log(aud_size_before, aud_size_after, clean_result)
values(v_aud_size_before, 0, '清理失败：'||sqlerrm);
dbms_output.put_line('['||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'] AUD$表清理失败：'||sqlerrm);
end;
end if;
-- --------------------------
-- 步骤4：清理SYSAUX表空间AWR快照（保留最近7天，删除更早的）
-- --------------------------
-- 获取数据库ID
select dbid into v_dbid from v$database;
-- 获取7天前的快照ID范围
select min(snap_id), max(snap_id), count(1)
into v_min_snap_id, v_max_snap_id, v_snap_count
from dba_hist_snapshot
where dbid = v_dbid
and end_interval_time < sysdate - 7; -- 保留最近7天
if v_snap_count > 0 then -- 存在需清理的快照
begin
-- 删除指定范围的AWR快照
dbms_workload_repository.drop_snapshot_range(
low_snap_id => v_min_snap_id,
high_snap_id => v_max_snap_id,
dbid => v_dbid
);
-- 写入清理日志
insert into awr_clean_log(dbid, min_snap_id, max_snap_id, clean_count, clean_result)
values(v_dbid, v_min_snap_id, v_max_snap_id, v_snap_count, '清理成功');
dbms_output.put_line('['||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'] AWR快照清理完成，共清理'||v_snap_count||'个快照（ID范围：'||v_min_snap_id||'-'||v_max_snap_id||'）');
exception
when others then
insert into awr_clean_log(dbid, min_snap_id, max_snap_id, clean_count, clean_result)
values(v_dbid, v_min_snap_id, v_max_snap_id, v_snap_count, '清理失败：'||sqlerrm);
dbms_output.put_line('['||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'] AWR快照清理失败：'||sqlerrm);
end;
end if;
commit;
end;
/
三、定时任务配置（自动化执行）
-- 1. 创建定时任务：每小时执行一次存储过程
variable jobno number;
begin
dbms_job.submit(
job => :jobno,
what => 'proc_tbs_auto_manage;', -- 执行的存储过程
next_date => sysdate, -- 首次执行时间：立即执行
interval => 'sysdate + 1/24' -- 执行频率：每小时一次
);
commit;
end;
/
-- 2. 查看定时任务状态（记录Job号，用于后续管理）
select job, next_date, next_sec, failures, broken
from user_jobs
where what = 'proc_tbs_auto_manage;';
-- 示例输出（Job号为123）：
-- JOB NEXT_DATE NEXT_SEC FAILURES B
-- ---------- ------------------ ------------- ---------- -
-- 123 2024-08-16 15:30:00 15:30:00 0 N
-- 3. 手动执行任务（测试用）
begin
dbms_job.run(123); -- 替换为实际Job号
commit;
end;
/
-- 4. 暂停定时任务
begin
dbms_job.broken(123, true); -- 替换为实际Job号
commit;
end;
/
-- 5. 重启定时任务
begin
dbms_job.broken(123, false); -- 替换为实际Job号
dbms_job.run(123);
commit;
end;
/
-- 6. 删除定时任务（如需停用）
begin
dbms_job.remove(123); -- 替换为实际Job号
commit;
end;
/
四、脚本说明与扩展建议
功能说明：
- 自动监控表空间使用率，当使用率超过85%时，自动新增1GB数据文件并开启自动扩展；
- 定期清理SYSTEM表空间中超过5GB的AUD$审计表（11g+安全清理）；
- 定期清理SYSAUX表空间中超过7天的AWR快照，保留近期性能数据。
扩展建议：
- 根据实际环境调整阈值（如扩容阈值85%、AUD$清理阈值5GB、AWR保留7天）；
- 新增邮件告警功能（通过UTL_MAIL），在扩容或清理失败时通知管理员；
- 对临时表空间（TEMP）单独添加监控与扩容逻辑（参考数据文件逻辑，使用dba_temp_files）；
- 定期备份清理日志表（audit_clean_log、awr_clean_log），避免日志表过大。
注意事项：
- 执行脚本需SYSDBA权限；
- 新增数据文件路径需确保数据库用户有写入权限；
- 清理操作前建议备份关键数据（如AUD$表、AWR快照）。