分享一個Oracle表空間自動擴容與清理腳本
一、基礎環境準備（首次執行）
-- 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快照）。
浙公網安備 33010602011771號