Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2
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:
- Reduce the amount of monitored objects in EM
- Resize the SYSAUX tablespace according to the recommendations in above table.
ALTER DATABASE DATAFILE '/path/systemSID.dbf' RESIZE 1000M;
- Resize the SYSTEM tablespace
ALTER DATABASE DATAFILE '/path/sysauxSID.dbf' RESIZE 1000M;