Oracle – Ramifications of Full SYSAUX Tablespace

oracletablespaces

What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?

There are plenty of sites discussing how to view the SYSAUX occupants, to reduce the size of SYSAUX via dbms_stat.purge_stats(), to add datafiles to SYSAUX tablespace, and to alter_stats_history_retention to avoid the problem in the future. This is not what is being asked here.

What are the consequences of not doing any of the previously mentioned tasks, but still utilizing the database? I've read that SYSAUX is important, but not critical (critical stuff is in SYSTEM). However, I have not seen any further discussion about what are the consequences of continuing to operate with a full SYSAUX.

For example, will performance be an issue? If so, in what ways?

Does Oracle stop writing to SYSAUX or does Oracle try to delete just enough "old" items to make room for "new" items? (most likely it depends on what it is, but don't see any documentation or article that describes the resulting behavior of a full SYSAUX tablespace and what the resulting consequences may be and what to watch out for).

If one gathers stats will Oracle purge the oldest historical stats to make room for the new ones? If so, is it true MMON gets called to do this "on-the-fly" rather than during the nightly process to maintain the retention setting. If so, is this costly if an application is doing a lot of stat gathering and to what extent is it costly?

Thanks in advance.

EDIT: Please assume that AUDSYS schema is not in SYSAUX tablespace.

Best Answer

The answer to a post mentioned on the side bar guided me to the following.

In

The Oracle® Database
Database Administrator’s Guide
21c
F31835-05
February 2021

in the chapter 11.12 Managing the SYSAUX Tablespace one can read

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

The view V$SYSAUX_OCCUPANTS lists the occupants of the SYSAUX tablespace. In a 12.1 database I found the following

SQL> select OCCUPANT_DESC from V$SYSAUX_OCCUPANTS
     order by 1;

OCCUPANT_DESC
----------------------------------------------------------------
AUDSYS schema objects
Analytical Workspace Object Table
Automated Maintenance Tasks
DB audit tables
Enterprise Manager Monitoring User
Enterprise Manager Repository
Expression Filter System
LogMiner
Logical Standby
OLAP API History Tables
OLAP Catalog
Oracle Multimedia ORDDATA Components
Oracle Multimedia ORDPLUGINS Components
Oracle Multimedia ORDSYS Components
Oracle Multimedia SI_INFORMTN_SCHEMA Components
Oracle Spatial
Oracle Streams
Oracle Text
Oracle Transparent Session Migration User
Oracle Ultra Search
Oracle Ultra Search Demo User
PL/SQL Identifier Collection
SQL Management Base Schema
Server Manageability - Advisor Framework
Server Manageability - Automatic Workload Repository
Server Manageability - Optimizer Statistics History
Server Manageability - Other Components
Statspack Repository
Transaction Layer - SCN to TIME mapping
Unified Job Scheduler
Workspace Manager
XDB

31 rows selected.

On a test database you can set the SYSAUX tablespace to read only on a test database and see what happens.

Oracle Support Note "How To Recreate the SYSAUX Tablespace (Doc ID 468116.1)" says

So dropping and creating only SYSAUX tablespace is not the supported actions, it similar to the SYSTEM tablespace i.e., its a mandatory tablespace.

Related Question