分享一個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,                      -- 數據庫IDmin_snap_id number,               -- 清理的最小快照IDmax_snap_id number,               -- 清理的最大快照IDclean_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;             -- 數據庫IDv_min_snap_id number;      -- 最小快照IDv_max_snap_id number;      -- 最大快照IDv_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_gbfrom (-- 表空間總容量及最大可擴展容量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) dleft 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_namewhere 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) loopv_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');exceptionwhen others theninsert 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天，刪除更早的）-- ---------------------------- 獲取數據庫IDselect 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_countfrom 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||'）');exceptionwhen others theninsert 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;
begindbms_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. 手動執行任務（測試用）
begindbms_job.run(123);  -- 替換為實際Job號commit;
end;
/-- 4. 暫停定時任務
begindbms_job.broken(123, true);  -- 替換為實際Job號commit;
end;
/-- 5. 重啟定時任務
begindbms_job.broken(123, false);  -- 替換為實際Job號dbms_job.run(123);commit;
end;
/-- 6. 刪除定時任務（如需停用）
begindbms_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快照）。