sysaux 表空间使用率异常，到底是什么数据导致呢？请查询如下sql：

set line 150
col Schema for a30
col Item for a30
col "Move Procedure" for a40

SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1 ;

上图明显为awr占用空间
修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
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

修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改

SQL> begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 10080,
topnsql => 100
);
end;

删除AWR快照，再次查看SYSAUX表空间使用率
select min(snap_id),max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
23544 24278

select dbid from v$database;

DBID
----------
477233887

begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 23544,
high_snap_id => 24278,
dbid => 477233887);
end;
/

ITPub博客

sysaux tablespace清理 in oracle11.2.0.3

最新文章