SYSTEM Tablespace Issues

Introduction:

The SYSTEM tablespace contains the data dictionary for the entire database.

- mandatory tablespace
- cannot be dropped,
- cannot be taken offline,
- do not create objects in the SYSTEM tablespace.

Issue#1

SYSTEM Tablespace Using MSSM Is Abnormally Growing And The Clusters C_TOID_VERSION# and/ or C_OBJ#_INTCOL# Are Using Most Of The Space.

Cause:

Bug 6390838 : C_OBJ#_INTCOL# KEEP GROWING WITH EXCHANGE PARTITIONS AND TTS IMPORT


Identification:

Identify which segments are using most of the space, and you find that the cluster(s) C_TOID_VERSION# and/ or C_OBJ#_INTCOL# is/ are using most of the space.

Here you can find that symptom, In this screenshot that is not available. Just to clarify the output of the command, I have put this screenshot.

Solution:

1. Install Patch 6390838 if available for your DB version and platform.

OR

2. Install 10.2.0.5 where the bug is fixed.

OR

3. Upgrade to 11.2 where the bug doesn't exist.

Issue#2

Moving or Reorganizing SYS Owned Objects to different tablespace or within SYSTEM tablespace

Questions:

Can SYS owned objects be moved to different tablespace or within SYSTEM tablespace ( using ALTER TABLE .. MOVE / ALTER INDEX .. REBUILD ) ? What is the way to reorganize SYS owned user or data dictionary objects ?


Answer:

Moving SYS owned object is not supported.

SYS owned (data dictionary objects) should not be moved, their storage attributes should not be changed. 

There may be specific guidelines to reclaim space by moving some SYS owned objects (AUD$ for example ) to another tablespace , but in general reorganizing sys objects is not supported.


The ideal and supported solution - 

1. Recreate a database using TTS ( Transportable TableSpace ).
2. Do full database export, recreate the database and then re-import.