Oracle 应对 System 与 Sysaux 表空间不足难题 --转发：https://mp.weixin.qq.com/s/uC4h6UITkeWuKSa-cRw-2w

一、审计aud$表导致system用满

1.进行相关常用检查的SQL

--查看Oracle表空间大小
SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",
free / (1024 * 1024 * 1024) "shengyu (G)",
(total - free) / (1024 * 1024 * 1024) "used (G)",
round((total - free) / total, 4) * 100 "rote %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
--查看system表空间哪个对像占用空间最大
SELECT  * FROM
    ( SELECT
            bytes,
            segment_name,
            segment_type,
            owner
        FROM
            dba_segments
        WHERE
            tablespace_name = 'SYSTEM'
        ORDER BY
            bytes DESC
    )
WHERE
    ROWNUM < 10;

--查看审计日志是否开启的SQL语句
select name,type,value from v$parameter where name like '%aud%';

show parameter audit查看数据库审计功能是否打开；
audit_trail值为DB，说明数据库审计功能打开，审计策略也是打开；
audit_sys_operations为FALSE说明可以审计非sys/system用户的所有操作，想要审计sys/system的操作，需要手动打开audit_sys_operations参数，即：
alter system set audit_sys_operations=TRUE scope=spfile;
然后再重启数据库。

--查看aud$表大小SQL
select bytes/1024/1024 MB,owner,segment_name,tablespace_name
from dba_segments
where segment_type='TABLE' and segment_name = 'AUD$';

--sys用户查看aud$表，如果sys用户登录提示权限不足可以查看v$pwfile_users或者更改密码(谨慎)
select count(*) from aud$;

2.查看那种审计占的最多

select action_name,count(*) from dba_audit_trail group by action_name;
一般是LOGON和LOGOFF类型的审计最多。看要求取消此类审计：

3.如果不想清理表，或者说就是要开审计，为了避免日志过多，可以选择性的取消审计内容

noaudit session whenever successful;一般来说，如果空间不是占的特别多，此类审计还是保留为好。
可以取消对一些登录特别频繁的用户的审计，比如DBSNMP用户
noaudit session by dbsnmp;

4.如果确实是没有用，推荐的操作是截断表，虽然有也官方的存储过程来清理，但效率较低，会成成大量undo及归档。

需要注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS：
1. 清空数据并保留原来的extents
TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;
在这里，REUSE STORAGE是TRUNCATE的一个参数，表示保持原来的存储不变。一般情况下，SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。

2. 逐步回缩extents(执行的时候根据实际情况调整每次回缩空间大小)
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;
2ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;
....
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

5.如果说要保留，但不想放到SYSTEM里，可以新建审计表空间，把aud$表迁移过去

--aud$表移动到新tablespace，&AUD_TBS_NAME表示新的表空间名
--迁移AUD$表
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘&AUD_TBS_NAME’);
END;
/

6.或者说是有额外的数据库审计产品，不再需要数据库开启审计，那么可以执行如下关闭审计

alter system set audit_trail=none scope=spfile;

之后需要重启生效，如果是RAC，可以滚动重启两个节点。

二、AWR导致SYSAUX表空间用满

SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，而假设SYSAUX表空间在默认条件下你假设不做不论什么配置，随着时间的推移。会膨胀的越来越大。

1.查看表空间使用情况

SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",
free / (1024 * 1024 * 1024) "shengyu (G)",
(total - free) / (1024 * 1024 * 1024) "used (G)",
round((total - free) / total, 4) * 100 "rote %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

2.查看sysaux表空间占比

col Item For a30
col "Space Used(GB)" For a10
col Schema For a20
col "MoveProcedure" For a200
SELECT occupant_name"Item",
          round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
          schema_name "Schema",
          move_procedure "MoveProcedure"
     FROM v$sysaux_occupants
   ORDER BY 2 Desc;

3.修改统计信息的保持时间

SQL> select dbms_stats.get_stats_history_retention from dual;
31
--修改统计信息保持时间
SQL> exec dbms_stats.alter_stats_history_retention(15);
SQL> select dbms_stats.get_stats_history_retention from dual;
15

4.修改AWR快照的保存时间

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

--检查当前系统的保留时间为8天,1小时採样一次
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL                           RETENTION                               TOPNSQL
------------------------------------------------- -------------------------------------------------
1494575446 +0000001:00:00.0                       +0000800:00:00.0                       DEFAULT

--修改awr快照保存时长
SQL> begin
        dbms_workload_repository.modify_snapshot_settings(
           interval => 60,
           retention => 10080,--分钟
           topnsql => 100
         );
end;
ORA-13541: 系统移动窗体基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 2

5.删除AWR快照

删除AWR快照，再次查看SYSAUX表空间使用率

--查询最最小和最大快照ID
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;
 MIN(SNAP_ID) MAX(SNAP_ID)
 ------------ ------------
       26705         27066

--注意，该方法有很大的坑，包底层是通过delete删除的会产生大量的redo，undo以及归档日志，会把空间撑满，可以使用其他手动删除方法
--删除最早的24个AWR快照，也就是最早的24小时的快照。（因为从八天变为7天减少24小时，所以手动删除第八天的awr快照，应该也可以等到时）
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id =>26705,high_snap_id => 26705+24);