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.
Best Answer
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:
Reference: V$SYSAUX_OCCUPANTS
The SYSAUX table is home for the following components:
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:
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:
Reference: The Data Dictionary
Solutions: