My Oracle 23/26ai

The SYSAUX tablespace contains auxiliary data for Oracle database operations:

Main contents:

AWR (Automatic Workload Repository) – Performance statistics and snapshots
Statspack – Legacy performance data (if used)
SQL Tuning Advisor – SQL tuning recommendations and history
Spatial data – Oracle Spatial metadata
OLAP – Online Analytical Processing metadata
Data Mining – Oracle Data Mining repository
Logical Standby – Logical standby metadata
Streams – Replication metadata
Ultra Search – Text search indexes
Enterprise Manager – EM repository data

Key points:

Purpose: Offloads non-critical system objects from the SYSTEM tablespace
Required: Cannot be dropped – it’s mandatory
Created: Automatically during database creation
Reduces load: Keeps SYSTEM tablespace cleaner and less fragmented

Example from 26ai

SELECT occupant_name, space_usage_kbytes/1024 MB
FROM V$SYSAUX_OCCUPANTS
ORDER BY space_usage_kbytes DESC;

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
SM/AWR                                                              318.625
SM/OPTSTAT                                                          156.875
SDO                                                                131.8125
XDB                                                                 98.8125
AO                                                                   55.125
AUDSYS                                                              42.5625
SM/ADVISOR                                                            21.75
SM/OTHER                                                             15.375
LOGMNR                                                              11.4375
SQL_MANAGEMENT_BASE                                                  8.8125
WM                                                                   7.3125

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
TEXT                                                                 4.3125
SMON_SCN_TIME                                                          3.25
PL/SCOPE                                                             3.0625
JOB_SCHEDULER                                                        2.6875
STREAMS                                                                   2
SQL_FIREWALL/LOGS                                                      .875
SM/SQLMON/REPORTS                                                     .6875
SQL_FIREWALL                                                          .6875
AUTO_TASK                                                             .6875
XSOQHIST                                                                  0
STATSPACK                                                                 0

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
ORDIM/ORDDATA                                                             0
ORDIM/ORDPLUGINS                                                          0
ORDIM/SI_INFORMTN_SCHEMA                                                  0
EM                                                                        0
XSAMD                                                                     0
ULTRASEARCH                                                               0
ULTRASEARCH_DEMO_USER                                                     0
EXPRESSION_FILTER                                                         0
EM_MONITORING_USER                                                        0
TSM                                                                       0
AUDIT_TABLES                                                              0

OCCUPANT_NAME                                                            MB
—————————————————————- ———-
LOGSTDBY                                                                  0
ORDIM                                                                     0

35 rows selected.

Quiz

Which tablespace in the CDB contains the Automatic Workload Repository (AWR)? (answer at the bottom)

a. Temp
b. Undo
c. SYSTEM
d. SYSAUX

STUDY TIP – it’s important to know the correct answer to any question, but more important to know why the other answers were incorrect!

TEMP (Temporary Tablespace)
Contains:

Temporary data for sorting operations
Hash joins, GROUP BY, ORDER BY operations
Index creation temporary data
Temporary tables (CREATE GLOBAL TEMPORARY TABLE)

Key: Data is session-specific and automatically deleted when operation completes or session ends. Not permanently stored.

UNDO (Undo Tablespace)
Contains:

Before-image of data (old values before changes)
Used for transaction rollback
Read consistency (other users see data as it was before uncommitted changes)
Flashback queries

Key: Stores the “undo” information needed to reverse uncommitted transactions or provide consistent reads.

SYSTEM (System Tablespace)
Contains:

Data dictionary tables (metadata about all database objects)
System catalog (users, tables, columns, privileges, etc.)
PL/SQL stored procedures, packages, triggers
Core Oracle database metadata
SYS and SYSTEM schema objects

Key: The heart of the database – contains all metadata that defines the database structure. Cannot be dropped or taken offline (except during maintenance).

Quick summary:

TEMP = Scratch space for queries
UNDO = Transaction history for rollback
SYSTEM = Database brain (all metadata)

In addition to testing the answers yourself, you can create your own “quizzes” by signing up for a free account on quizlet.com. Try some flashcards I made for SYSAUX to increase your understanding!

https://quizlet.com/1122253488/oracle-database-sysaux-tablespace-contents-and-usage-flash-cards/?i=40cirv&x=1jqt