如何解决SYSAUX空间问题

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