本文為墨天輪數據庫管理服務團隊第111期技術分享，內容原創，作者為技術顧問肖傑，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。
場景：Oracle19c SYSAUX表空間使用率不足，且無存儲擴容，需要手動清理表空間下的數據。
SYSAUX是SYSTEM表空間的輔助表空間，SYSTEM表空間包含系統的數據字典和數據庫的管理信息，輔助表空間SYSAUX則用於附加的數據庫組件，例如，OEM庫（Oracle Enterprise Manager Repository）、AWR快照信息庫、統計信息、審計信息等。
一、查詢表空間信息
SQL> set lin 300 pagesize 999
SQL> col TABLESPACE_NAME for a30
SQL> select B.tablespace_name,sumb total_gb,Nvl(suma,0) free_gb,(sumb-Nvl(suma,0)) used_gb, to_char(Nvl(round((sumb-suma)/sumb*100,2),100),'990.99') "used%" from (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) suma from dba_free_space group by tablespace_name) A, (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),3) sumb from dba_data_files group by tablespace_name) B where A.tablespace_name(+)=B.tablespace_name order by 5;
TABLESPACE_NAME TOTAL_GB FREE_GB USED_GB used%
------------------------------ ---------- ---------- ---------- -------
USERS 2.005 2 .005 0.25
SYSTEM 16 14.88 1.12 7.00
UNDOTBS2 1.195 .8 .395 33.05
UNDOTBS1 .825 .38 .445 53.94
SYSAUX 31 .67 30.33 97.84二、查看磁盤組使用率
SQL> col DISKGROUP_NAME for a40
SQL> SELECT
name AS diskgroup_name,
TOTAL_MB / 1024 AS TOTAL_MB_gb,
(TOTAL_MB - FREE_MB) / 1024 AS used_space_gb,
FREE_MB / 1024 AS free_space_gb,
ROUND(((TOTAL_MB - FREE_MB) / TOTAL_MB) * 100, 2) AS usage_percentage
FROM v$asm_diskgroup;
DISKGROUP_NAME TOTAL_MB_GB USED_SPACE_GB FREE_SPACE_GB USAGE_PERCENTAGE
---------------------------------------- ----------- ------------- ------------- ----------------
ARCHIVEDG 4096 85.3515625 4010.64844 2.08
DATADG1 22528 22443.2578 84.7421875 99.62
DATADG2 20480 20459.0859 20.9140625 99.9
MGMT 200 25.5234375 174.476563 12.76
OCR_VOTE 60 .89453125 59.1054688 1.49由此可見磁盤組使用率已達上限，無法擴容，只能清理表空間數據。
三、查看佔用SYSAUX表空間較大的對象
SQL> SELECT occupant_name "Component",
space_usage_kbytes / 1024 "Space Used (MB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY "Space Used (MB)" DESC;
Component Space Used (MB) Schema Move Procedure
------------------------- --------------- ------------------ -------------------------------------------
SM/AWR 26359.6875 SYS
SDO 264.0625 MDSYS MDSYS.MOVE_SDO
SM/OPTSTAT 241.375 SYS
SM/ADVISOR 221.9375 SYS
SM/OTHER 179.8125 SYS
XDB 66.0625 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
AO 45.8125 SYS DBMS_AW.MOVE_AWMETA
JOB_SCHEDULER 16.375 SYS
LOGMNR 10.8125 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
WM 6.5625 WMSYS DBMS_WM.move_proc
SMON_SCN_TIME 3.5625 SYS
PL/SCOPE 2.875 SYS
TEXT 2.8125 CTXSYS DRI_MOVE_CTXSYS
SQL_MANAGEMENT_BASE 2.6875 SYS
AUDSYS 2 AUDSYS DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
STREAMS 1.6875 SYS
LOGSTDBY 1.5625 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
EM_MONITORING_USER 1.375 DBSNMP
AUTO_TASK .5625 SYS
ORDIM/ORDDATA 0 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
EM 0 SYSMAN emd_maintenance.move_em_tblspc
STATSPACK 0 PERFSTAT
ULTRASEARCH 0 WKSYS MOVE_WK
ORDIM 0 ORDSYS ordsys.ord_admin.move_ordim_tblspc
EXPRESSION_FILTER 0 EXFSYS
XSOQHIST 0 SYS DBMS_XSOQ.OlapiMoveProc
TSM 0 TSMSYS
XSAMD 0 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
32 rows selected. 由此可見，佔用 SYSAUX 表空間的主要對象是 SM/AWR 。
註釋：SM/AWR (Server Manageability - Automatic Workload Repository): 主要存儲 AWR 快照信息。
四、清理方法
以下方法均必須以SYS用户登錄數據庫。
方法1：自動清理（推薦）
修改awr保留日期，數據庫會在維護窗口自動清理（一般在22:00以後）
-- 查詢當前的awr信息保留時長（45天），默認保留8天
SQL> SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00045 00:00:00.0
Elapsed: 00:00:00.02
-- 修改為保留15天
SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 15 * 24 * 60
);
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
-- 驗證結果：
SQL> SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00015 00:00:00.0
Elapsed: 00:00:00.01方法2：手動清理
使用Oracle官方包 DBMS\_WORKLOAD\_REPOSITORY.DROP\_SNAPSHOT\_RANGE。
示例1：
SQL> SELECT MIN(snap_id),begin_interval_time FROM dba_hist_snapshot;
MIN(SNAP_ID)
------------
83858
-- 查詢需要保留的天數之前的最大 snap_id。及保留的天數，此處為15天
SQL> SELECT MAX(snap_id) FROM dba_hist_snapshot WHERE end_interval_time < SYSDATE - 15;
MAX(SNAP_ID)
------------
85304
-- 按照查詢的snap_id開始清理：
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => 83858,
high_snap_id => 85304
);
END;
/
PL/SQL procedure successfully completed.示例2：
-- 查看最小/最大快照ID/DBID
SQL> SELECT DBID,MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot group by DBID;
DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
1694393302 520 523
-- 保留7天快照（清理舊數據）
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => [最小ID],
high_snap_id => [最大ID]-168, -- 保留168小時(7天)
dbid => [DBID]
);
END;
/
--示例
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => 520,
high_snap_id => 523 - 3,
dbid => 1694393302
);
END;
/
PL/SQL procedure successfully completed.五、結果驗證
本次採用方法1進行處理，次日查看錶空間結果如下，SYSAUX表空間使用率已經成功降低一半。
08:47:21 SQL> col TABLESPACE_NAME for a30
08:47:21 SQL> Select B.tablespace_name,sumb total_gb,Nvl(suma,0) free_gb,(sumb-Nvl(suma,0)) used_gb, to_char(Nvl(round((sumb-suma)/sumb*100,2),100),'990.99') "used%" from (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) suma from dba_free_space group by tablespace_name) A, (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),3) sumb from dba_data_files group by tablespace_name) B where A.tablespace_name(+)=B.tablespace_name order by 5;
TABLESPACE_NAME TOTAL_GB FREE_GB USED_GB used%
------------------------------ ---------- ---------- ---------- -------
USERS 2.005 2 .005 0.25
SYSTEM 16 14.88 1.12 7.00
UNDOTBS2 1.195 .9 .295 24.69
SYSAUX 31 17.95 13.05 42.10
UNDOTBS1 .825 .4 .425 51.52六、其他案例
1、檢查表空間使用情況：
SQL> set lin 300 pagesize 999
SQL> col TABLESPACE_NAME for a30
SQL> Select B.tablespace_name,sumb total_gb,Nvl(suma,0) free_gb,(sumb-Nvl(suma,0)) used_gb, to_char(Nvl(round((sumb-suma)/sumb*100,2),100),'990.99') "used%" from (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) suma from dba_free_space group by tablespace_name) A, (Select tablespace_name,round(sum(bytes)/(1024*1024*1024),3) sumb from dba_data_files group by tablespace_name) B where A.tablespace_name(+)=B.tablespace_name order by 5;
TABLESPACE_NAME TOTAL_GB FREE_GB USED_GB used%
------------------------------ ---------- ---------- ---------- -------
UNDO_2 5 5 0 0.00
UNDO_TBS1 5 5 0 0.00
SYSTEM 30.342 26.39 3.952 13.02
SYSAUX 30.308 2.23 28.078 92.64
... ...2、查看錶空間下對象佔用情況
SQL> SELECT occupant_name "Component",
space_usage_kbytes / 1024 "Space Used (MB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY "Space Used (MB)" DESC;
Component Space Used (MB) Schema Move Procedure
------------------------ --------------- ------------------------------------------------------------
SM/ADVISOR 3128.125 SYS
SM/OTHER 2664.0625 SYS
SM/OPTSTAT 664.5 SYS
SDO 114.125 MDSYS MDSYS.MOVE_SDO
XDB 60.625 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
AO 44.6875 SYS DBMS_AW.MOVE_AWMETA
JOB_SCHEDULER 19.0625 SYS
WM 6.5625 WMSYS DBMS_WM.move_proc
SMON_SCN_TIME 3.375 SYS
TEXT 2.8125 CTXSYS DRI_MOVE_CTXSYS
AUDSYS 2 AUDSYS DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
SQL_MANAGEMENT_BASE 1.125 SYS
SM/AWR .8125 SYS
PL/SCOPE .75 SYS
AUTO_TASK .5625 SYS
EM_MONITORING_USER .1875 DBSNMP
LOGSTDBY .125 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
STREAMS .0625 SYS
STATSPACK 0 PERFSTAT
ORDIM/ORDDATA 0 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
EM 0 SYSMAN emd_maintenance.move_em_tblspc
XSAMD 0 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
ULTRASEARCH 0 WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
EXPRESSION_FILTER 0 EXFSYS
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
TSM 0 TSMSYS
LOGMNR 0 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
XSOQHIST 0 SYS DBMS_XSOQ.OlapiMoveProc
ORDIM 0 ORDSYS ordsys.ord_admin.move_ordim_tblspc可以看到，該案例中佔用SYSAUX的主要對象為：SM/ADVISOR 、SM/OTHER 和 SM/OPTSTAT
SM/ADVISOR：SQL調優數據
SM/OTHER：其他數據
SM/OPTSTAT：統計信息組件
3、清理SQL調優數據：
-- 查詢當前有哪些優化任務：
SQL> select ADVISOR_NAME,count(*) from dba_advisor_tasks group by ADVISOR_NAME ;
ADVISOR_NAME COUNT(*)
------------------------ ---------
SQL Performance Analyzer 1
SQL Access Advisor 1
Segment Advisor 111611
SPM Evolve Advisor 2
Statistics Advisor 2
-- 查詢30天前的段的調優任務
SQL> SELECT task_name, created
FROM dba_advisor_tasks
WHERE advisor_name = 'Segment Advisor'
AND created < SYSDATE - 30
ORDER BY created;
-- 清理 Segment Advisor 歷史任務
SQL> DECLARE
CURSOR c_tasks IS
SELECT task_name, created
FROM dba_advisor_tasks
WHERE advisor_name = 'Segment Advisor'
AND created < SYSDATE - 30; -- 保留30天內任務
BEGIN
FOR r_task IN c_tasks LOOP
-- 使用正確的包和過程
DBMS_ADVISOR.DELETE_TASK(r_task.task_name);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
-- 清理 Segment Advisor 歷史任務，安全增強建議（針對大量任務）：
SQL> DECLARE
TYPE task_list IS TABLE OF VARCHAR2(128);
l_tasks task_list;
BEGIN
-- 每次處理1000個任務，避免redo、undo日誌膨脹
SELECT task_name
BULK COLLECT INTO l_tasks
FROM dba_advisor_tasks
WHERE advisor_name = 'Segment Advisor'
AND created < SYSDATE - 30
AND ROWNUM <= 1000; -- 每次1000條
FOR i IN 1..l_tasks.COUNT LOOP
DBMS_ADVISOR.DELETE_TASK(l_tasks(i));
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
-- 如空間未立即釋放：手動重組表釋放空間：（維護窗口）
ALTER TABLE WRI$_ADV_OBJECTS MOVE; -- Segment Advisor主要存儲表
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;4、清理統計信息歷史信息：
-- 查看當前統計信息保留策略（當前31天）
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
GET_STATS_HISTORY_RETENTION
---------------------------
31
-- 縮短保留期至7天（立即生效）
SQL> EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);
PL/SQL procedure successfully completed.
-- 修改後確認
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
GET_STATS_HISTORY_RETENTION
---------------------------
7
-- 立即清理過期統計信息
SQL> EXEC DBMS_STATS.PURGE_STATS(SYSDATE - 7);
PL/SQL procedure successfully completed.5、清理其他數據
此處以 SQL Tuning Advisor 信息為例：
-- 查詢10天前的SQL調優任務
SQL> SELECT task_name, created
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Tuning Advisor'
AND created < SYSDATE - 10
ORDER BY created;
TASK_NAME CREATED
----------------------------- -------------
SYS_AUTO_SQL_TUNING_TASK 20250601
-- 使用PL/SQL塊逐個刪除10天前的任務，如果任務數量很多，可以分批刪除，避免長時間佔用資源
SQL>BEGIN
FOR task_rec IN (
SELECT task_name
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Tuning Advisor'
AND created < SYSDATE - 10
) LOOP
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => task_rec.task_name);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service
