Oracle sysaux表空间使用率高处理

mahan

Oracle sysaux表空间使用率81%，由于存储空间紧张，sysaux表空间总共才4GB。下面详细讲解清理表空间方法，希望对大家有所帮助。

处理过程
查看SYSAUX表空间内各组件的空间占用排行
COLUMN occupant_name FORMAT a30
COLUMN schema_name FORMAT a15
SELECT
    occupant_name,
    schema_name,
    space_usage_kbytes / 1024 AS usage_mb,
    round(space_usage_kbytes / (select sum(space_usage_kbytes) from v$sysaux_occupants where space_usage_kbytes > 0) * 100, 2) pct
FROM v$sysaux_occupants
WHERE space_usage_kbytes > 0
ORDER BY space_usage_kbytes DESC;
执行过程如下：

OCCUPANT_NAME                  SCHEMA_NAME       USAGE_MB        PCT
------------------------------ --------------- ---------- ----------
SM/AWR                         SYS                   1205      51.88
SM/OPTSTAT                     SYS               372.0625      16.02
SDO                            MDSYS             223.8125       9.64
SM/ADVISOR                     SYS                178.625       7.69
AUDSYS                         AUDSYS            121.9375       5.25
XDB                            XDB                 68.375       2.94
SM/OTHER                       SYS                 67.125       2.89
AO                             SYS                45.8125       1.97
LOGMNR                         SYSTEM                  11        .47
WM                             WMSYS               6.5625        .28
JOB_SCHEDULER                  SYS                 5.6875        .24
SMON_SCN_TIME                  SYS                   3.25        .14
PL/SCOPE                       SYS                 2.8125        .12
TEXT                           CTXSYS              2.8125        .12
SQL_MANAGEMENT_BASE            SYS                 2.6875        .12
STREAMS                        SYS                 1.6875        .07
LOGSTDBY                       SYSTEM              1.5625        .07
EM_MONITORING_USER             DBSNMP              1.1875        .05
AUTO_TASK                      SYS                  .5625        .02

19 rows selected.

SQL>
重点关注：SM/AWR (Server Manageability - Automatic Workload Repository) 和 SM/OPTSTAT (Optimizer Statistics) 的占用情况。SM/AWR 排名第一且占用空间大。

检查当前AWR快照设置和保留策略
COLUMN snap_interval FORMAT a20
COLUMN retention FORMAT a20
SELECT
    snap_interval,
    retention
FROM dba_hist_wr_control;
执行过程如下：

SQL> select SNAP_INTERVAL ,retention
  2  FROM dba_hist_wr_control;

SNAP_INTERVAL                                                               RETENTION
--------------------------------------------------------------------------- --------------------
+00000 01:00:00.0                                                           +00008 00:00:00.0

SQL>
可以看到awr快照默认保留8天。由于空间紧张，这里需要把awr快照修改为只保留3天。

修改AWR保留策略
BEGIN
    DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 4320); --单位是分钟，修改为保留3天awr数据，4320=3*24*60
END;
/
执行过程如下：

SQL>  BEGIN
  2  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 4320);
  3  end;
  4  /
BEGIN
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (259200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
ORA-06512: at line 2


SQL>
检查类型为 MOVING_WINDOW 的 AWR 基线
SQL> SELECT baseline_name, moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';  2    3  

BASELINE_NAME                                   MOVING_WINDOW_SIZE
----------------------------------------------- ------------------
SYSTEM_MOVING_WINDOW                            8                                                                                                   

SQL>

可以看到，类型为 MOVING_WINDOW 的 AWR 基线保留时间是8天。需要修改为3天。

修改类型为 MOVING_WINDOW 的 AWR 基线
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(window_size => 3); --单位是天，修改为3天
END;
/
执行过程如下：

SQL> BEGIN
  2    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(window_size => 3);
  3  END;
  4  /

PL/SQL procedure successfully completed.
再次修改AWR保留策略
SQL> BEGIN
  2      DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 4320);
  3  END;
  4  /

PL/SQL procedure successfully completed.
检查数据库中的awr快照
SELECT snap_id,dbid,instance_number,TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') begin_time,TO_CHAR(end_interval_time,'YYYY-MM-DD HH24:MI:SS') end_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
执行过程如下：

<此处省略>
      2514 1197336244               2 2025-10-06 04:00:42 2025-10-06 05:00:00
      2513 1197336244               1 2025-10-06 04:00:00 2025-10-06 05:00:12
      2513 1197336244               2 2025-10-06 03:00:23 2025-10-06 04:00:42
      2512 1197336244               1 2025-10-06 03:00:35 2025-10-06 04:00:00
      2512 1197336244               2 2025-10-06 02:00:05 2025-10-06 03:00:23
      2511 1197336244               1 2025-10-06 02:00:17 2025-10-06 03:00:35
      2511 1197336244               2 2025-10-06 01:00:46 2025-10-06 02:00:05
      2510 1197336244               1 2025-10-06 01:00:58 2025-10-06 02:00:17
      2510 1197336244               2 2025-10-06 00:00:28 2025-10-06 01:00:46
      2509 1197336244               1 2025-10-06 00:00:40 2025-10-06 01:00:58
      2509 1197336244               2 2025-10-05 23:00:10 2025-10-06 00:00:28
      2508 1197336244               1 2025-10-05 23:00:21 2025-10-06 00:00:40
      2508 1197336244               2 2025-10-05 22:00:51 2025-10-05 23:00:10
      2507 1197336244               1 2025-10-05 22:00:03 2025-10-05 23:00:21
      2507 1197336244               2 2025-10-05 21:00:32 2025-10-05 22:00:51
      2506 1197336244               1 2025-10-05 21:00:44 2025-10-05 22:00:03
      2506 1197336244               2 2025-10-05 20:00:14 2025-10-05 21:00:32
      2505 1197336244               1 2025-10-05 20:00:26 2025-10-05 21:00:44
      2505 1197336244               2 2025-10-05 19:00:56 2025-10-05 20:00:14
      2504 1197336244               1 2025-10-05 19:00:08 2025-10-05 20:00:26
      2504 1197336244               2 2025-10-05 18:00:37 2025-10-05 19:00:56
      2503 1197336244               1 2025-10-05 18:00:49 2025-10-05 19:00:08
      2503 1197336244               2 2025-10-05 17:00:19 2025-10-05 18:00:37
      2502 1197336244               2 2025-10-05 16:00:01 2025-10-05 17:00:19
      2502 1197336244               1 2025-10-05 17:00:31 2025-10-05 18:00:49
      2501 1197336244               1 2025-10-05 16:00:12 2025-10-05 17:00:31
      2501 1197336244               2 2025-10-05 15:00:42 2025-10-05 16:00:01
      2500 1197336244               1 2025-10-05 15:00:00 2025-10-05 16:00:12
      2500 1197336244               2 2025-10-05 14:00:24 2025-10-05 15:00:42
      2499 1197336244               1 2025-10-05 14:00:35 2025-10-05 15:00:00
      2499 1197336244               2 2025-10-05 13:00:05 2025-10-05 14:00:24
      2498 1197336244               2 2025-10-05 12:00:47 2025-10-05 13:00:05
      2498 1197336244               1 2025-10-05 13:00:17 2025-10-05 14:00:35
      2497 1197336244               2 2025-10-05 11:00:28 2025-10-05 12:00:47
      2497 1197336244               1 2025-10-05 12:00:59 2025-10-05 13:00:17
      2496 1197336244               1 2025-10-05 11:00:40 2025-10-05 12:00:59
      2496 1197336244               2 2025-10-05 10:00:10 2025-10-05 11:00:28
      2495 1197336244               1 2025-10-05 10:00:21 2025-10-05 11:00:40
      2495 1197336244               2 2025-10-05 09:00:51 2025-10-05 10:00:10
      2494 1197336244               2 2025-10-05 08:00:33 2025-10-05 09:00:51
      2494 1197336244               1 2025-10-05 09:00:03 2025-10-05 10:00:21
      2493 1197336244               2 2025-10-05 07:00:14 2025-10-05 08:00:33
      2493 1197336244               1 2025-10-05 08:00:45 2025-10-05 09:00:03
      2492 1197336244               1 2025-10-05 07:00:26 2025-10-05 08:00:45
      2492 1197336244               2 2025-10-05 06:00:56 2025-10-05 07:00:14
      2491 1197336244               2 2025-10-05 05:00:37 2025-10-05 06:00:56
      2491 1197336244               1 2025-10-05 06:00:07 2025-10-05 07:00:26
      2490 1197336244               1 2025-10-05 05:00:49 2025-10-05 06:00:07
      2490 1197336244               2 2025-10-05 04:00:19 2025-10-05 05:00:37
      2489 1197336244               1 2025-10-05 04:00:31 2025-10-05 05:00:49
      2489 1197336244               2 2025-10-05 03:00:01 2025-10-05 04:00:19
      2488 1197336244               2 2025-10-05 02:00:42 2025-10-05 03:00:01
      2488 1197336244               1 2025-10-05 03:00:13 2025-10-05 04:00:31
      2487 1197336244               2 2025-10-05 01:00:24 2025-10-05 02:00:42
      2487 1197336244               1 2025-10-05 02:00:00 2025-10-05 03:00:13
      2486 1197336244               1 2025-10-05 01:00:36 2025-10-05 02:00:00
      2486 1197336244               2 2025-10-05 00:00:06 2025-10-05 01:00:24
      2485 1197336244               1 2025-10-05 00:00:17 2025-10-05 01:00:36
      2485 1197336244               2 2025-10-04 23:00:47 2025-10-05 00:00:06
      2484 1197336244               1 2025-10-04 23:00:59 2025-10-05 00:00:17
      2484 1197336244               2 2025-10-04 22:00:29 2025-10-04 23:00:47
      2483 1197336244               2 2025-10-04 21:00:10 2025-10-04 22:00:29
      2483 1197336244               1 2025-10-04 22:00:41 2025-10-04 23:00:59     

430 rows selected.

SQL>
手工删除快照
语法:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id    IN  NUMBER,
   high_snap_id   IN  NUMBER,
   dbid           IN  NUMBER   DEFAULT NULL);

BEGIN
   dbms_workload_repository.drop_snapshot_range(low_snap_id => 2483,high_snap_id => 2514);  
END;
/
执行过程如下：

SQL> BEGIN
  2     dbms_workload_repository.drop_snapshot_range(low_snap_id => 2483,high_snap_id => 2514);  
  3  END;
  4  /

PL/SQL procedure successfully completed.
可以按照上面的方法继续删除其他不需要的快照。

附录：查询表空间下面的对象占用情况SQL
--这个sql适用于查询任何表空间下面的对象
col segment_name format a50
col segment_type format a20
set lines 200
set pages 10000
select segment_name,segment_type,round(sum(bytes)/1024/1024,2) as "size(MB)"
from dba_segments
where tablespace_name='SYSAUX'  --查询其他表空间下的对象时，替换表空间名称即可
Group by segment_name,segment_type
Order by 3 ;