78.21 sysaux表空间80%
都是转的
--首选查看下这个表空间近期是否有过暴增
set linesize 640
set pagesize 36
col snap_id for 999999
col con_id for 99999
col pdbname for a16
col ts_name for a20
col rtime for a18
col ts_size_mb for 999999.9
col ts_used_mb for 999999.9
col pct_used for 99.99
select u.snap_id,
to_char(s.begin_interval_time, 'yyyy-mm-dd hh24') begin_time,
to_char(s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
t.name,
round(u.tablespace_size * ts.block_size / 1024 / 1024, 2) ts_size_mb,
round(u.tablespace_usedsize * ts.block_size / 1024 / 1024, 2) ts_used_mb,
round((u.tablespace_size - u.tablespace_usedsize) * ts.block_size / 1024 / 1024, 2) ts_free_mb,
round(u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
from dba_hist_tbspc_space_usage u,
v$tablespace t,
dba_hist_snapshot s,
dba_tablespaces ts
where u.tablespace_id = t.ts#
and u.snap_id = s.snap_id
and t.name = ts.tablespace_name
and s.instance_number = 1
and t.name = upper('&tablespace_name')
and s.end_interval_time > sysdate - 7
order by snap_id desc;
--维度1 并没有暴增 然后分析SYSAUX表空间中，统计AWR/统计信息/审计信息数据占用了多少空间：
SET LINESIZE 720;
SET PAGESIZE 36;
COL "Item" FOR A16;
COL SCHEMA_NAME FOR A30;
SELECT OCCUPANT_NAME "Item",
SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
SCHEMA_NAME ,
MOVE_PROCEDURE
FROM V$SYSAUX_OCCUPANTS
WHERE SPACE_USAGE_KBYTES > 1048576
ORDER BY "Space Used (GB)" DESC;
根据OCCUPANT_NAME列的数据来判断：
如果该列为SM/AWR， 它是Server Manageability - Automatic Workload Repository的缩写，那么表示AWR信息占用空间的大小
如果该列为SM/OPTSTAT，它是Server Manageability - Optimizer Statistics History的缩写，那么表示统计信息占空间的大小
如果该列为AUDIT_TABLES，表示审计信息占用的空间大小。
如果该列为LOGMNR，表示logminer产生的数据占用了表空间
上面是从高一点的维度分析，其实我们还可以继续下钻分析SYSAUX表空间下面哪些对象/表占用了大量的空间，一般只需关注最大的几个表，例如AUD$,WRH$_ACTIVE_SESSION_HISTORY,WRR$_REPLAY_CALL_INFO
--维度2 查看表空间里面里面TOP 50大小的数据库对象
SET LINESIZE 720;
SET PAGESIZE 36;
COL OWNER FOR A16;
COL SEGMENT_TYPE FOR A20;
COL SEGMENT_NAME FOR A38;
COL TABLESPACE_NAME FOR A20;
SELECT *
FROM (SELECT OWNER,
SEGMENT_TYPE,
SEGMENT_NAME,
ROUND(SUM(BYTES) / 1024 / 1024/1024,2) TOTAL_SIZE_GB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME =UPPER('&TABLESPACE_NAME')
GROUP BY OWNER,
SEGMENT_TYPE,
SEGMENT_NAME,
TABLESPACE_NAME
ORDER BY TOTAL_SIZE_GB DESC)
WHERE ROWNUM <= 50;
通过上面两个维度，我们基本就可以知道那些组件/对象占用了SYSAUX的表空间，我们可以从这几个方面/维度继续分析/定位原因了
--处理方法 审计信息占用过大 如果审计信息占用了大量空间(SM/AWR),那么一般表现为AUD$表非常大，考虑取消某些审计，调整审计级别/粒度设置。如果公司政策或规范要求非常严格，那么只能扩展表空间大小。
--可以用下面SQL查询/确认那种审计占用了大量空间
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的数据空间占用分布信息
@$ORACLE_HOME/rdbms/admin/awrinfo.sql
--关于调整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=>724*60);
--在以上设置完成后，可以删除不需要的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;
--统计信息占用过大 如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; --查询统计信息的保留时间
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); --设置统计信息的保留时间
--如果发现统计信息占用了SYSAUX表空间的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理或调整保留周期。
exec dbms_stats.purge_stats(sysdate-&days);
--设置历史统计信息保留天数
exec dbms_stats.alter_stats_history_retention(&day);