如何解决SYSAUX空间问题

郑全

Troubleshooting Issues with SYSAUX Space Usage (Doc ID 1399365.1)

In this Document
Purpose
Troubleshooting Steps

Basic Diagnostics

Space Usage

Purge Issues

Maintenance and Setup

Known Issues

Troubleshooting Other Issues

Community Discussions
References

Applies to:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Purpose
This article provides guidance for troubleshooting issues with the SYSAUX tablespace. This tablespace is the repository for all auxiliary database metadata related to Oracle options and features. See:
Document 243246.1 SYSAUX New Mandatory Tablespace in Oracle 10g and higher
Troubleshooting Steps

Basic Diagnostics
It is recommended to gather information as per the following SRDC document:
Document 2326852.1 SRDC - How to Collect Standard Information for an AWR SYSAUX Storage Issue Using TFA Collector (Recommended) or Manual Steps
A primary user of the SYSAUX tablespace is the Automatic Workload Repository. For general issues with SYSAUX a very useful report is the "awrinfo" report which outputs not only general Automatic Workload Repository (AWR) information such as the size, data distribution, etc. in AWR and SYSAUX. but also information about any object stored in SYSAUX. This report can be found under the Oracle Home directory in the rdbms/admin subdirectory. It is collected as part of the standard information in the article above, but can be executed standalone as follows:
SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql
Even if the issue is not AWR itself, eliminating it can be useful , allowing focus to be made on other possibilities.
Note that use of the awrinfo.sql script accesses Automatic Workload Repository data and therefore requires an Oracle Diagnostics Pack license. See:
Document 1490798.1 AWR Reporting - Licensing Requirements Clarification



Space Usage
Most issues with SYSAUX relate to the usage of space. The following articles can help in this regard together with the output from the awrinfo.sql script mentioned above to determine which components are using the most space:
Document 552880.1 General Guidelines for SYSAUX Space Issues
Document 1292724.1 Suggestions if your SYSAUX Tablespace grows rapidly or too large
Document 287679.1 Space Management In Sysaux Tablespace with AWR in Use
Document 1271178.1 Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes
Document 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
Document 1499542.1 Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace

Document 556183.1 SYSAUX tablespace grows quite fast due to Apply spilling
Document 396502.1 High Storage Consumption for LOBs in SYSAUX Tablespace
Document 814710.1 WRI$_OPTSTAT_SYNOPSIS$ is rapidly filling the SYSAUX tablespace
Document 1243058.1 Wrh$_sql_plan table growth causes Sysaux Tablespace size increase continuously


Purge Issues
Space usage can be streamlined by the purging of unwanted data. The following article provide advice in this area:
Document 1055547.1 SYSAUX Grows Because Optimizer Stats History is Not Purged
Document 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged
Document 454678.1 Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging
Document 1360000.1 Abnormal High Space Usage in Sysaux Tablespace - Unable to Purge


Maintenance and Setup
The following articles provide guidance on various aspects of maintenance and setup of the SYSAUX tablespace:
Document 301186.1 How to relocate the SYSAUX TABLESPACE
Document 950128.1 Fixing a Corrupted SYSAUX Tablespace
Document 782974.1 How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?
Document 1119758.1 How to move Oracle Spatial objects from SYSAUX tablespace to a user defined tablespace
Document 1339425.1 How To Address An "ORA-01950: no privileges on tablespace 'SYSAUX'" Error?
Known Issues
Document 8553944.8 Bug 8553944 - SYSAUX tablespace grows
Document 10279045.8 Bug 10279045 - Slow Statistics purging (SYSAUX grows)
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 12958182.8 Bug 12958182 - SYSAUX tablespace grows (mon_mods_all$ and col_usage$)

Troubleshooting Other Issues
For guidance troubleshooting other performance issues see:
Document 1377446.1 Troubleshooting Performance Issues