今天在检查一台测试环境的表空间时，发现SYSAUX的使用率已经达到99.91%
TABLESPACE_NAME FILES Freesize(MB) Usedsize(MB) Filesize(MB) Filemaxsize(MB) CurrentUsed(%) MaxUsed(%)
------------------------------ ---------- ------------ ------------ ------------ --------------- -------------- ----------
SYSAUX 2 28.3125 32939.671875 32967.984375 32967.984375 99.91412122840 99.9141212
清理AWR统计数据
- 首先查看是什么使用了空间
SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc;
Item Space Used (GB) Schema Move Procedure
---------------------------------------------------------------- --------------- ---------------------------------------------------------------- ----------------------------------------------------------------
SM/AWR 4.1289672851562 SYS
XDB 0.1832885742187 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SM/OPTSTAT 0.1232299804687 SYS
SDO 0.0913696289062 MDSYS MDSYS.MOVE_SDO
- 既然显示为AWR占用最多，查看下统计数的保存天数
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
- 因为数据库修改过一次dbid，实际存在两个部分的AWR信息
SQL> select dbid, min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;
DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
3187895652 10357 10495
2750849929 1 54
- 清空上一个dbid下的所有snapshot
SQL> desc dbms_workload_repository.drop_snapshot_range
Parameter Type Mode Default?
------------ ------ ---- --------
LOW_SNAP_ID NUMBER IN
HIGH_SNAP_ID NUMBER IN
DBID NUMBER IN Y
SQL> exec dbms_workload_repository.drop_snapshot_range(10357,10495,3187895652)
PL/SQL procedure successfully completed
SQL> select dbid, min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;
DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
2750849929 1 54
- 再删除历史统计数据
SQL> exec dbms_stats.purge_stats(sysdate-5);
PL/SQL procedure successfully completed
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
20-8月 -16 04.52.17.000000000 下午 +08:00
SQL> exec dbms_stats.purge_stats(sysdate-3);
PL/SQL procedure successfully completed
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
22-8月 -16 05.54.49.000000000 下午 +08:00
- 清除信息后，未见表空间占用减少，这时要考虑高水位的问题，对几张stat相关的表做一下表收缩。采样数据库都以WRM(_*和WRH)_*的格式命名。
SQL> alter table WRH$_SQLSTAT shrink space;
表已更改。
SQL> alter table WRH$_SYSSTAT shrink space;
表已更改。
SQL> alter table WRH$_SEG_STAT shrink space;
表已更改。
SQL> alter table WRH$_LATCH shrink space;
表已更改。
当然并不是所有的表都支持shrink，参考此文。
这样操作之后，占用率降到96.7%。看来实际的大头并不是AWR信息。
处理LOB
- 使用dba_segments试图查询下这个表空间下哪个段占用最大的空间。
SQL> select segment_name, segment_type, sum(bytes)/1024/1024 from dba_segments where tablespace_name='SYSAUX' group by segment_name,segment_type order by sum(bytes) desc;
SEGMENT_NAME SEGMENT_TYPE SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- ------------------ --------------------
SYS_LOB0000091751C00014$$ LOB PARTITION 27593.625
WRH$_SYSSTAT_PK INDEX PARTITION 460.0625
WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 394.0625
WRH$_LATCH_PK INDEX PARTITION 380.0625
WRH$_EVENT_HISTOGRAM TABLE PARTITION 242.0625
WRH$_PARAMETER_PK INDEX PARTITION 210.0625
WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 202.0625
WRH$_PARAMETER TABLE PARTITION 186.0625
- 查看LOB字段属于哪个表
SQL> select owner,table_name,column_name from all_lobs where segment_name='SYS_LOB0000091751C00014$$';
OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- ---------------
AUDSYS CLI_SWP$a9b5f52c$1$1 LOG_PIECE
- 单独看这张表，竟然提示表不存在
SQL> desc audsys.cli_swp$a9b5f52c$1$1
ERROR:
ORA-04043: 对象 audsys.cli_swp$a9b5f52c$1$1 不存在
-
网络搜索原来是12C的新特性Unified Audit存放的审计数据
参考http://www.orafaq.com/node/2894，即时是sys用户也不能对这张表做DML操作。 -
而里面的数据应该是通过unified_audit_trail视图也读取的
我在测试环境上使用select count(*) from unified_audit_trail;，过了14分钟也不能获得数据，最后以ORA-01652临时表空间用完退出。 -
既然不能直接删除表的数据，Oracle提供了存储过程的方式清除这张审计表的数据
SQL> begin
2 dbms_audit_mgmt.clean_audit_trail(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
4 use_last_arch_timestamp => FALSE);
5 end;
6 /
PL/SQL 过程已成功完成。
删除后，SYSAUX的空间使用率一下手降到了12.5%。
进一步操作
按照Oracle文档，我们可以如此手工清除unified audit的数据，也可以定时进行删除，也可以关闭unified audit功能。
具体可以参考此文档