技术分享 | Oracle19c SYSAUX表空间清理

本文为墨天轮数据库管理服务团队第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