What to Do When the Oracle SYSTEM Tablespace is Full

When faced with a situation where the SYSTEM tablespace in Oracle is full, it’s crucial to act quickly to prevent database performance and stability issues. Here are the steps I followed to identify and resolve the problem:

1. Identify the Largest Segments

First, you need to identify which segments are consuming the most space in the SYSTEM tablespace. Use the following SQL query to gather this information:

SELECT SUM(bytes)/1024/1024 AS MB, segment_name
FROM dba_segments
WHERE tablespace_name='SYSTEM'
GROUP BY segment_name
ORDER BY 1 DESC;

2. Analyze the Results

In my case, I discovered that the SYS.AUD$ table had grown significantly. This table stores Oracle audit data, which can accumulate quickly in busy environments.

3. Immediate Solution: Truncate the SYS.AUD$ Table

A quick way to free up space is to truncate the SYS.AUD$ table. However, note that truncating this table will remove all audit records. Be sure this action complies with your organization’s auditing policies. The command to truncate the table is:

TRUNCATE TABLE SYS.AUD$;

Important: Avoid truncating any other objects in the SYSTEM tablespace, as this could lead to the loss of critical data.

4. Alternative: Delete Old Data

An alternative approach is to delete older audit records (e.g., those older than 30 days). However, due to the large volume of data in the SYS.AUD$ table, this operation can consume a lot of UNDO space and negatively impact system performance. The command to delete old records would be:

DELETE FROM SYS.AUD$ WHERE timestamp# < SYSDATE - 30;

Due to the potential impact, I decided against using this option in my specific case.

Conclusion

When dealing with a full SYSTEM tablespace, the most direct and effective solution is to truncate the SYS.AUD$ table, which will quickly free up space. However, keep in mind that this will result in the loss of all audit data, so it must be carefully considered. Deleting old audit data is a valid approach, but it should be used with caution due to the potential impact on UNDO space.