处理过程
查看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;
执行过程如下：
修改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
附录：查询表空间下面的对象占用情况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 ;