5

SYSTEM and SYSAUX Tablespace size of my DB is in the following status now:

SYSTEM  - PCT_USED= 99
SYSAUX  - PCT_USED= 95

I believe the db is in critical situation now. What can I do to bring back it to the normal condition and what are the standard ways that should follow to keep SYSTEM and SYSAUX tablespace files safe.

Any help will be appreciated.

4
  • what is 'allocated size' and 'allocated free space' for both SYSAUX and SYSTEM. You can check through OEM. After connection to OEM 11g ,Then server->tablespaces. within that you can check your all details about SYSTEM and SYSAUX. Jul 24, 2017 at 7:32
  • if DB is in critical state, first, ADD more space so it wont stop working. Then figure out why it is consuming space. You don't say if your tablespace size is 100M or 20G so 99% is probably meaningless to provide any helpful resolution other than adding space.
    – Raj
    Jul 24, 2017 at 13:55
  • where do you see SYSTEM - PCT_USED= 99 and SYSAUX - PCT_USED= 95 ?
    – miracle173
    Jul 24, 2017 at 16:38
  • 1
    In addition to all the other very fine comments about sizing, there are two common culprits of segments taking space in SYSTEM that should not be. First is the all-too-common practice of allowing local schemas to create segments there. Bad. The only schema that should have segments in SYSTEM are SYS and SYSTEM. The other is the audit table sys.aud$ and its indexes. By default they are in SYSTEM but you should use dbms_audit_mgmt to move them to their own dedicated TS.
    – EdStevens
    Jul 24, 2017 at 21:32

6 Answers 6

7

The SYSTEM and SYSAUX tablespaces are reuqired by the RDBMS to function correctly.

The SYSAUX tablespace

A list of objects that reside in the SYSAUX tablespace can be retrieved using the V$SYSAUX_OCCUPANTS view. This view displays the following information:

OCCUPANT_NAME        VARCHAR(64)    Occupant name  
OCCUPANT_DESC        VARCHAR(64)    Occupant description  
SCHEMA_NAME          VARCHAR2(64)   Schema name for the occupant  
MOVE_PROCEDURE       VARCHAR2(64)   Name of the move procedure; null if not applicable  
MOVE_PROCEDURE_DESC  VARCHAR2(64)   Description of the move procedure  
SPACE_USAGE_KBYTES   NUMBER         Current space usage of the occupant (in KB)  

Reference: V$SYSAUX_OCCUPANTS

The SYSAUX table is home for the following components:

SYSAUX Table                         Previous Location
------------------------------------ --------------------------
Analytical Workspace Object Table    SYSTEM
Enterprise Manager Repository        OEM_REPOSITORY
LogMiner                             SYSTEM
Logical Standby                      SYSTEM
OLAP API History Tables              CWMLITE
Oracle Data Mining                   ODM
Oracle Spatial                       SYSTEM
Oracle Streams                       SYSTEM
Oracle Text                          DRSYS
Oracle Ultra Search                  DRSYS
Ora interMedia ORDPLUGINS Comp.      SYSTEM
Ora interMedia ORDSYS Components     SYSTEM
Ora interMedia SI_INFORMTN_SCHEMA C. SYSTEM
Server Manageability Components      -
Statspack Repository                 User-defined
Oracle Scheduler                     - 
Workspace Manager                    SYSTEM

Reference: Database Components and the SYSAUX Tablespace

If you can reduce the size of the data being stored by the components, then you can reduce the size of the required SYSAUX tablespace.

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The following size recommendations are provided by Oracle to determine the AWR size and to a large portion the size of the SYSAUX tablespace:

Number of CPUs                                 2    |     8    |    32
Number of concurrently active sessions        10    |    20    |   100
Number of user objects: tables and indexes   500    | 5,000    |50,000
Estimated SYSAUX size with default config    500 MB |     2 GB |     5 GB

Reference: Controlling the Size of the SYSAUX Tablespace

If your database has been constantly growing, or if you are monitoring a lot of objects with Enterprise Management Console, then your SYSAUX tablespace will slowly fill up.

The SYSTEM tablespace

The SYSTEM tablespace always contains the data dictionary tables for the entire database. So if your database grows in size due to new objects, then your SYSTEM tablespace will require more space too.

Data Dictionary

What does the data dictionary contain:

  • The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  • How much space has been allocated for, and is currently used by, the schema objects
  • Default values for columns
  • Integrity constraint information
  • The names of Oracle users
  • Privileges and roles each user has been granted
  • Auditing information, such as who has accessed or updated various schema objects
  • Other general database information

Reference: The Data Dictionary

Solutions:

  1. Reduce the amount of monitored objects in EM
  2. Resize the SYSAUX tablespace according to the recommendations in above table.
    • ALTER DATABASE DATAFILE '/path/systemSID.dbf' RESIZE 1000M;
  3. Resize the SYSTEM tablespace
    • ALTER DATABASE DATAFILE '/path/sysauxSID.dbf' RESIZE 1000M;
1

As per ToadWorld blog Here The SYSAUX Tablespace should have a minimum size of 250M. SYSAUX Tablespace needs to be PERMANENT with extent management local and ASSM (Automatic Segment Space Management). The SYSAUX Tablespace cannot be made read only. Hence, proper care should be taken while creating the SYSAUX tablespace as the tablespace attributes are not modifiable once these are set.

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database.

The SYSAUX tablespace is occupied by a number of database components, The SYSAUX tablespace is occupied by a number of database components.

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The space consumed by the AWR is determined by several factors, including the number of active sessions in the system at any given time, the snapshot interval, and the historical data retention period. A typical system with an average of 10 concurrent active sessions may require approximately 200 to 300 MB of space for its AWR data.

As per Oracle Blog Here The following table provides guidelines on sizing the SYSAUX tablespace based on the system configuration and expected load.

Parameter/Recommendation                           Small    Medium  Large
Number of CPUs                                        2       8      32
Number of concurrently active sessions                10      20     100
Number of user objects: tables and indexes            500     5,000 50,000
Estimated SYSAUX size at steady state with default configuration    
                                                      500 MB    2 GB    5 GB

I am also attaching the screen shot of OEM 11g , To check the space occupied details of SYSTEM and SYSAUX. for you ref here enter image description here

Managing Occupants size of the SYSAUX Tablespace

The V$SYSAUX_OCCUPANTS view manages occupants in the SYSAUX tablespace. This view allows you to monitor the space usage of occupant application objects in the SYSAUX tablespace.

SELECT occupant_name, space_usage_kbytes FROM v$sysaux_occupants;

I am also attaching the screen shot for your refenter image description here

1

SYSAUX and SYSTEM are no different to any other tablespace in that they need to sized right. In my experience, space pressure in SYSAUX is pretty much always down to AWR data. Try running awrinfo.sql to find the culprit.

Also have a dig around on support.oracle.com . There are known issues with AWR retention/purge settings not playing nice (e.g doc 1912201.1)

0

First of all check which table is taking the most space in those tablespace. There are certain tables in SYSTEM and SYSAUX tablespaces that store historical query data, AWR snapshot and audit information, which can be deleted/truncated.

If you are not sure or concerned if deletion of records in a table would affect something else, then just add extra space in those tablespaces.

0

One option is to use the autoextensible feature of the datafiles of your tablespaces.

alter database datafile '[name]' autoextend on;

but be careful of using this feature, as buggy software can use up your allocated space quickly. If you have operating system space monitoring, then autoextend could work for you.

You can also use the space monitoring features of OEM to send you a notification when your tablespaces reach a certain percentage, for example 80%. Do not wait until 95% or higher to act upon adding space.

You can also set resumable_timeout parameter so that sessions waiting for space wait for you to add space and do not end abnormally.

You can truncate sys.aud$ periodically. If you need to keep auditing records, back it up before truncating.

Your db is never 'critical' when you are about to run out of space. You always have the option of sqlplus / or sqlplus /nolog if sysaux runs out of space.

0

This query will help you to track what is in sysaux.

COLUMN space_mb      FORMAT A12
COLUMN occupant_desc FORMAT A55
COLUMN schema        FORMAT A30
SELECT occupant_desc, TO_CHAR(space_usage_kbytes/1024, '999,999.00') space_mb,
substr(schema_name,1,30) Schema
FROM v$sysaux_occupants
ORDER BY 2 desc;

You may also want to query dba_segments filtering by table space name, looking for the largest object. You should look specifically at the sys.aud$ table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.