sysaux表空间增长，分析原因
背景:我管理的生产数据库最近做了一个大版本的升级，从11g升级到了19c。升级后有sysaux表空间使用率的报警。表空间使用率已经达到阈值。先增加了1000mb，之后一周，又涨到了阈值。又增加了1000mb。之前11g的时候4gb很稳定，没有这样的增长状态。所以想在网上找找原因，顺便学习一下：
我找到的文章：
https://blog.itpub.net/26736162/viewspace-2152868/
https://www.cnblogs.com/liulianzhen99/articles/17680197.html
有可能的原因：
-
审计信息占用过大
这个原因在我的生产库不存在，因为没有开启审计功能。
检查方法：
select action_name,count(*) from dba_audit_trail group by action_name;
一般是LOGON和LOGOFF类型的审计数据最多。可以截断占用空间最大的AUD$表（需要确认审计信息是否需要保留）
--截断AUD$
truncate table sys.aud$ reuse storage;
alter table sys.aud$ deallocate unused keep xxxm; -
AWR数据占用过大
这个的可能性大。
如果AWR数据占用过大，那么一般情况下是由于AWR信息没有及时清理引起的.
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql
通过上面SQL，你可以得到AWR的数据空间占用分布信息，如下例子所示(部分信息)
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 7,096.3 MB ( 43% of 16,384.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 6,961.3 MB ( 98.1% )
| Schema XDB occupies 62.9 MB ( 0.9% )
| Schema AUDSYS occupies 50.3 MB ( 0.7% )
| Schema SYSTEM occupies 12.6 MB ( 0.2% )
| Schema WMSYS occupies 6.6 MB ( 0.1% )
| Schema GSMADMIN_INT occupies 1.4 MB ( 0.0% )
| Schema DBSNMP occupies 1.2 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 5,137.9 MB
| AUDIT_TABLES SYS 1,262.0 MB
| XDB XDB 62.9 MB
| AUDSYS AUDSYS 50.3 MB
| SM/OTHER SYS 49.9 MB
| SM/ADVISOR SYS 48.2 MB
| SM/OPTSTAT SYS 14.8 MB
| LOGMNR SYSTEM 10.8 MB
| JOB_SCHEDULER SYS 8.9 MB
| WM WMSYS 6.6 MB
| SMON_SCN_TIME SYS 3.3 MB
| PL/SCOPE SYS 2.9 MB
| SQL_MANAGEMENT_BASE SYS 2.7 MB
| AO SYS 1.9 MB
| STREAMS SYS 1.7 MB
| LOGSTDBY SYSTEM 1.6 MB
| EM_MONITORING_USER DBSNMP 1.2 MB
| AUTO_TASK SYS 0.6 MB
| EM SYSMAN 0.0 MB
| EXPRESSION_FILTER EXFSYS 0.0 MB
| ORDIM ORDSYS 0.0 MB
| ORDIM/ORDDATA ORDDATA 0.0 MB
| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB
| SDO MDSYS 0.0 MB
| STATSPACK PERFSTAT 0.0 MB
| TEXT CTXSYS 0.0 MB
| TSM TSMSYS 0.0 MB
| ULTRASEARCH WKSYS 0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB
| XSAMD OLAPSYS 0.0 MB
| XSOQHIST SYS 0.0 MB
|
| Others (Unaccounted space) 428.3 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema GSMADMIN_INT occupies 1.4 MB
|
| Total space 1.4 MB
|
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM 426.9 MB
|
| Total space 426.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 30 mins snapshot INTERVAL:
| AWR size/day 168.6 MB (3,596 K/snap * 48 snaps/day)
| AWR size/wk 1,180.0 MB (size_per_day * 7) per instance
|
| Estimates based on 48 snaps in past 24 hours:
| AWR size/day 168.6 MB (3,596 K/snap and 48 snaps in past 24 hours)
| AWR size/wk 1,180.0 MB (size_per_day * 7) per instance
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
ASH 1,400.3 27.3 980 45.9 321.6 87% : 13%
FIXED 1,348.6 26.2 944 44.2 309.7 45% : 55%
EVENTS 455.8 8.9 319 15.0 104.7 42% : 58%
SQLPLAN 392.0 7.6 274 12.9 90.0 65% : 35%
SQLBIND 240.0 4.7 168 7.9 55.1 50% : 50%
SQL 100.7 2.0 70 3.3 23.1 66% : 34%
SPACE 86.3 1.7 60 2.8 19.8 63% : 37%
SQLTEXT 16.9 0.3 12 0.6 3.9 94% : 6%
RAC 0.6 0.0 0 0.0 0.1 50% : 50%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
ASH 265.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_SESSION_HISTORY_37363 - 99% TABLE PARTITION
ASH 257.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_SESSION_HISTORY_37363 - 97% TABLE PARTITION
..........................................................
主要有几种情况：
1：AWR数据保留周期太长。这种情况可以通过设置AWR的保留时间来减少AWR信息的存储空间。如果是默认的保留周期，不建议这样做。
2：一些数据库设置或问题引起的。例子，碰到过一起案例，由于网络调整，导致数据库中某些使用了dblink的SQL出现大量和长时间的 'SQL*Net break/reset to client'等待，导致MMON进程采集了大量这些SQL存储在WRH$_ACTIVE_SESSION_HISTORY中，导致这个表的数据从某个时间点后采集了大量的数据。
关于调整AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：
SELECT * FROM DBA_HIST_WR_CONTROL;
通过如下的SQL语句可以设置AWR信息的保留时间为N天（例如：72460），每隔1小时收集一次AWR信息：
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>72460);
在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => xxx,
HIGH_SNAP_ID => xxx,
DBID => xxxx);
END;
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放空间归还给SYSAUX表空间。此时，应该对相关的大表执行降低高水位线操作来释放空间。
还有一些非常规操作，这些最好不要在生产环境操作，可用于测试环境或紧急情况下使用：
set linesize 680
col sql_cmd for a90;
select distinct 'truncate table '||segment_name||';' as sql_cmd
,s.bytes/1024/1024 as table_size
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
更多详细信息可以参考学习资料[1]
