Understanding the SYSTEM and SYSAUX Tablespaces in Oracle Database

Understanding SYSTEM and SYSAUX tablespaces is essential for proper Oracle Database administration. These are the “heart” and “auxiliary circulatory systems” of the database — critical to keeping it running smoothly. This guide covers basic concepts, troubleshooting, and best practices, ideal for both beginners and experienced professionals.

1. SYSTEM Tablespace – The Database “Heart”

1.1 What Is the SYSTEM Tablespace?

The SYSTEM tablespace contains essential information needed to run the database, including:

Data Dictionary – metadata on all database objects (tables, indexes, users, roles), e.g., DBA_TABLES, DBA_USERS, DBA_DATA_FILES.
Built-in PL/SQL Packages – core packages like DBMS_OUTPUT, DBMS_STATS, UTL_FILE.
Recovery Metadata – data used alongside control files and redo logs for recovery.

Common misconception:
Avoid storing application data in SYSTEM—it can fill up and cause the database to stop.

2. SYSAUX Tablespace – The “Auxiliary Circulation System”

2.1 What Is the SYSAUX Tablespace?

SYSAUX, introduced in Oracle Database 10g, offloads tasks from SYSTEM and stores data for:

AWR snapshots (performance monitoring).
Enterprise Manager repository data.
Various components: Oracle Spatial, Text, Scheduler, etc.

Why SYSAUX exists:
It shifts non-essential data out of SYSTEM to lighten its workload and simplify management.

3. SYSTEM vs. SYSAUX – A Comparison

Attribute	SYSTEM	SYSAUX
Purpose	Core database functions	Auxiliary/optional components
Contains	Data dictionary, PL/SQL	AWR, EM repo, text, spatial, scheduler
Dependencies	Required for DB operation	Depends on enabled options
Management Level	High	Moderate

4. Daily Management Tips for SYSTEM & SYSAUX

4.1 Checking Tablespace Usage

Monitor available space to prevent issues:

-- SYSTEM usage
SELECT tablespace_name,
    ROUND((TOTAL_BYTES - FREE_BYTES)/1024/1024,2) AS USED_MB,
    ROUND(FREE_BYTES/1024/1024,2) AS FREE_MB,
    ROUND((TOTAL_BYTES - FREE_BYTES)/TOTAL_BYTES*100,2) AS USED_PCT
FROM (
    SELECT tablespace_name, SUM(bytes) TOTAL_BYTES
    FROM dba_data_files GROUP BY tablespace_name
), (
    SELECT tablespace_name FREE_NAME, SUM(bytes) FREE_BYTES
    FROM dba_free_space GROUP BY tablespace_name
)
WHERE tablespace_name = FREE_NAME
  AND tablespace_name = 'SYSTEM';

-- SYSAUX usage (same structure, adjust tablespace_name = 'SYSAUX')

4.2 Handling Space Shortage

Resize existing datafiles:

ALTER DATABASE DATAFILE '/path/to/system01.dbf' RESIZE 1G;
ALTER DATABASE DATAFILE '/path/to/sysaux01.dbf' RESIZE 1G;

Add new datafile to SYSAUX:

ALTER TABLESPACE SYSAUX
  ADD DATAFILE '/path/to/sysaux02.dbf'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

4.3 Removing Unnecessary Data

SYSAUX can grow large due to logs and snapshots:

Delete AWR snapshots:

EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
  low_snap_id => 100,
  high_snap_id => 200
);

5. Troubleshooting SYSTEM & SYSAUX Issues

5.1 SYSTEM is Full

If SYSTEM is 100% full, the database can stop. Emergency action:

ALTER DATABASE DATAFILE '/path/to/system01.dbf' RESIZE 2G;

5.2 SYSAUX Growth Spike

Sudden growth often stems from AWR or EM data. Regularly clean snapshots and remove old data to control size.

6. Best Practices for SYSTEM & SYSAUX

Enable AUTOEXTEND to avoid full tablespaces:

ALTER DATABASE DATAFILE '/path/to/sysaux01.dbf'
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Monitor usage regularly to plan capacity.
Purge old AWR and EM data to control growth.
Always take a full backup before modifying tablespaces or files.

7. Summary

The SYSTEM tablespace is the core of the Oracle DB.
The SYSAUX tablespace supports optional features and offloads SYSTEM.
Proper management ensures stable, efficient database operation.

Let me know if you’d like SQL “CREATE TABLE” and “INSERT” samples, diagrams for visual clarity, or additional troubleshooting scripts!

[reference]
13 Managing Tablespaces