Symptom
SM/OPTSTAT occupies the most space within SYSUAX Tablespace.
SQL> SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
OCCUPANT_NAME SPACE_USAGE_KBYTES
------------------------------------------------------------------
LOGMNR 93.824
LOGSTDBY 1.408
SMON_SCN_TIME 3.328
PL/SCOPE 1.600
STREAMS 1.024
AUDIT_TABLES 0
XDB 0
AO 1.408
XSOQHIST 1.408
XSAMD 0
SM/AWR 6.098.880
SM/ADVISOR 420.672
SM/OPTSTAT 31.647.360
Warning: This KBA is not dealing with space issues of SM/ADVISOR (e.g. huge growth and size of WRH$_ACTIVE_SESSION_HISTORY)!
Read more...
Environment
- Oracle Database Release 10g
- Oracle Database Release 11g
- Oracle Database Release 12c
- Oracle Database Release 18c
- Oracle Database Release 19c
- SAP Release independent
Keywords
AWR, Automatic Workload Repository, growth, space, SM/OPTSTAT, grow, statistic, retention, DBMS_STATS, PURGE_STATS, sysaux, waste, SM, OPTSTAT, WRI$, history, AWRInfo, AWRINFO.SQL , KBA , BC-DB-ORA , Oracle , BC-DB-ORA-DBA , Database Administration , How To
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.