美文网首页
如何解决SYSAUX空间问题

如何解决SYSAUX空间问题

作者: 重庆思庄 | 来源:发表于2020-02-10 19:45 被阅读0次

    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

    相关文章

      网友评论

          本文标题:如何解决SYSAUX空间问题

          本文链接:https://www.haomeiwen.com/subject/qpdzxhtx.html