sysaux是system的辅助表空间，主要存放AWR基表和审计信息，一般情况下sysaux的使用率都是正常的，看到sysaux表空间使用过高就有点怀疑了，首先查看下表空间。
SQL> select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX' group by owner,segment_name,segment_type order by 4 desc ) where rownum <10; 2 3 4 5 OWNER SEGMENT_NAME SEGMENT_TYPE GB ------------------------------ --------------------------------------------------------------------------------- ------------------ ---------- SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 13.1171875 SYS I_WRI$_OPTSTAT_H_ST INDEX 12.6362915 SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10.1123047 SYS WRH$_SQL_BIND_METADATA TABLE 7.53729248 SYS WRH$_SQL_BIND_METADATA_PK INDEX 5.27453613 SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2.31567383 SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1.94006348 SYS I_WRI$_OPTSTAT_HH_ST INDEX 1.75189209 MONITOR LOGIN_LOG TABLE 1.5625 9 rows selected. Elapsed: 00:02:05.03 SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY'; OWNER INDEX_NAME ------------------------------ ------------------------------ SYS I_WRI$_OPTSTAT_H_ST SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
所以是不是大概清楚了，WRI$_OPTSTAT_HISTGRM_HISTORY表占用了35GB，那这是 一个什么表或者存放的是什么信息呢，实际上这个表存放的是历史统计信息，默认存放31天，我们通过dbms_stats.get_stats_history_retention可以确认保存时间，他是通过脚本$ORACLE_HOME/rdbms/admin/catost.sql创建的。
SQL> col Item for a30 col Schema for a20 set lines 200 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;SQL> SQL> SQL> 2 3 4 5 6 Item Space Used (GB) Schema MoveProcedure ------------------------------ --------------- -------------------- ---------------------------------------------------------------- SM/OPTSTAT 42.144 SYS SM/AWR 16.19 SYS SM/ADVISOR .468 SYS EM .27 SYSMAN emd_maintenance.move_em_tblspc JOB_SCHEDULER .151 SYS XDB .124 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE SDO .073 MDSYS MDSYS.MOVE_SDO AO .037 SYS DBMS_AW.MOVE_AWMETA XSOQHIST .037 SYS DBMS_XSOQ.OlapiMoveProc SM/OTHER .02 SYS LOGMNR .013 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE ORDIM/ORDDATA .013 ORDDATA ordsys.ord_admin.move_ordim_tblspc SQL_MANAGEMENT_BASE .007 SYS XSAMD .005 OLAPSYS DBMS_AMD.Move_OLAP_Catalog EXPRESSION_FILTER .004 EXFSYS TEXT .004 CTXSYS DRI_MOVE_CTXSYS SMON_SCN_TIME .003 SYS WM .003 WMSYS DBMS_WM.move_proc PL/SCOPE .002 SYS EM_MONITORING_USER .002 DBSNMP STREAMS .001 SYS LOGSTDBY .001 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc ULTRASEARCH 0 WKSYS MOVE_WK ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK ORDIM 0 ORDSYS ordsys.ord_admin.move_ordim_tblspc ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc TSM 0 TSMSYS AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables AUTO_TASK 0 SYS STATSPACK 0 PERFSTAT 31 rows selected.
同样我们通过v$sysaux_occupants视图也能确认，SM/OPTSTAT代表的就是历史统计信息，可以看到保存31天的历史信息，接下来我们对历史的删除。
SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 15-FEB-21 11.13.29.763337000 PM +08:00 Elapsed: 00:00:00.06 SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 Elapsed: 00:00:00.01
SQL> exec dbms_stats.purge_stats(sysdate-10); PL/SQL procedure successfully completed. Elapsed: 01:42:52.33 SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 05-MAR-21 09.50.09.000000000 AM +08:00 Elapsed: 00:00:00.08
删除成功！！！！我们查看段大小。
SQL> select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX' group by owner,segment_name,segment_type order by 4 desc ) where rownum <10; 2 3 4 5 OWNER SEGMENT_NAME SEGMENT_TYPE GB ------------------------------ --------------------------------------------------------------------------------- ------------------ ---------- SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 13.1171875 SYS I_WRI$_OPTSTAT_H_ST INDEX 12.6362915 SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10.1123047 SYS WRH$_SQL_BIND_METADATA TABLE 7.53729248 SYS WRH$_SQL_BIND_METADATA_PK INDEX 5.27453613 SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2.31567383 SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1.94006348 SYS I_WRI$_OPTSTAT_HH_ST INDEX 1.75189209 MONITOR LOGIN_LOG TABLE 1.5625 9 rows selected. Elapsed: 00:02:05.03
并没有释放，原因就是dbms_stats.purge_stats(sysdate-10)操作实际的操作内容是delete。 所以.......... 是不是明白一些坑了。。 ①delete操作过程中有大量的undo占用和redo产生，关注归档使用量 ②delete之后不会释放HWM 使用MOVE方式释放HWM
SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move; Table altered. Elapsed: 00:00:30.58 SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online; Index altered. Elapsed: 00:02:08.88 SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online; Index altered. Elapsed: 00:00:55.97
(select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX' group by owner,segment_name,segment_type order by 4 desc ) where rownum <10; 2 3 4 5 OWNER SEGMENT_NAME SEGMENT_TYPE GB ------------------------------ --------------------------------------------------------------------------------- ------------------ ---------- SYS WRH$_SQL_BIND_METADATA TABLE 7.53729248 SYS WRH$_SQL_BIND_METADATA_PK INDEX 5.27453613 SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2.31567383 SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1.94006348 SYS I_WRI$_OPTSTAT_HH_ST INDEX 1.75189209 MONITOR LOGIN_LOG TABLE 1.5625 SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 1.21685791 SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 1.06835938 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX .76953125 9 rows selected. Elapsed: 00:02:43.54
———————————————— 版权声明：本文为CSDN博主「Small-A」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/renyanjie123/article/details/114832842
