sysaux表空间使用率过大（AWR）
背景：我负责管理的生产库，升级之后，sysaux表空间有大量的增长。我手动扩了2gb空间，感觉还是不够。
sysaux表空间持续增长的原因是AWR快照：
-
希望了解AWR快照的信息可以用：
sql>@?/rbdms/admin/awrinfo.sql这个脚本
脚本会统计出AWR的详细信息。当然我们不一定都需要这些信息。
可以知道每次AWR快照的数据量。我的是1075KB，每次 -
查询现在保留策略
col SNAP_INTERVAL for a30
col RETENTION for a20
col SRC_DBNAME for a20
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ---------- ---------- ---------- --------------------
4144310150 +00000 00:20:00.0 +00045 00:00:00.0 DEFAULT 0 4144310150 4144310150
保留策略是20分钟一次，保留45天
计算一下，一天需要72次，一天的AWR快照数据量是75.6MB，保留45天的数据量也能得到
- 将保留时间更改为28天
execute dbms_workload_repository.modify_snapshot_settings(interval => 20,retention => 40320);
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ---------- ---------- ---------- --------------------
4144310150 +00000 00:20:00.0 +00028 00:00:00.0 DEFAULT 0 4144310150 4144310150
- 查看sysaux使用率
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024 as MB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- -------------
SYSAUX 6220 4883.13 78.51%
SYSTEM 2000 1268.13 63.41%
UNDOTBS1 10000 496.25 4.96%
BIZ 178176 68217.5 38.29%
PORTAL 1024 29.94 2.92%
USERS 400 1 .25%
- 手动将过期的数据删除
----查询快照号
select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
207359 210613
----得到需要删掉的起始和结束的快照号：MIN & MAX
min max max-min 天数
207359 210613 3254 45.19444444
208597 210613 2016 28
exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 207359,high_snap_id => 210613,dbid => 4144310150);
----再查使用率
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------- ---------- ---------- -----------------------------------------
SYSAUX 6220 2027.94 32.6%
