2

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.

2
  • 1
    By default, things like your audit trail are in SYSAUX. If you are auditing (one of the most likely reasons for SYSAUX to grow in the first place) and SYSAUX runs out of space, it will halt your entire DB until you add space or relocate the audit trail to another tablespace with room to grow. – pmdba Feb 21 at 14:54
  • 1
    To add to @pmdba - take a look at the dbms_audit_mgmt package. It has procedures for moving the audit tables to their own dedicated TS and ongoing housekeeping of the audit tables so that they don't just grow forever. Once you've relocated the audit tables, you shouldn't have any worries with SYSAUX. – EdStevens Feb 21 at 17:52
0

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.

0

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

Nearly full? None at all.

Completely full? Probably a dead database!

By far the biggest consumer of SYSAUX in 19c is the Unified Audit Trail.
Where we used to dump gigabytes of Audit files into a file system somewhere and delete them occasionally, 19c now insists that we dump the same gigabytes of Audit data into tables held in SYSAUX, by default.

It has always been the case that if the Audit system cannot record what it wants to then it throws an almighty strop and crashes the entire database.
Nothing like subtlety.

So, if your Unified Audit Trail is writing to SYSAUX and SYSAUX fills up completely?
Bye bye, Database.

Oracle recommend changing the tablespace used by the Unified Audit Trail.

Kind of makes you wonder why they don't ship Oracle Database with a separate tablespace for this in the first place.

1
  • Let's presume full enough means not enough contiguous free space to allocate another extent to the SYSAUX tablespace. – Alex Bartsmon Feb 23 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.