SYSAUX Tablespace Growing Rapidly due to SCHEDULER$_JOB_OUTPUT Table and ORA-01031 Error in Automatic segment advisor task

Investigating Unexpected SYSAUX Tablespace Growth

Recently, I was asked to investigate an Oracle database where the SYSAUX tablespace was growing unexpectedly. Using V$SYSAUX_OCCUPANTS and DBA_SEGMENTS, I discovered that SYS.SCHEDULER$_JOB_OUTPUT had accumulated approximately 25 million rows and was consuming nearly 14 GB of space.

The cause was surprisingly simple: a scheduler job was executing DBMS_OUTPUT.PUT_LINE millions of times during each run, causing its output to be stored in the database. After stopping the unnecessary output and reclaiming the space, I noticed something else worth investigating.

The Automatic Segment Advisor was reporting ORA-01031: insufficient privileges errors inside its job output, even though the scheduler task was marked as successful. What started as a straightforward SYSAUX space issue turned into a second investigation involving Oracle privileges and internal advisor operations.

In this article, I’ll walk through both problems, the diagnostic queries I used, and the solutions I applied.

Identifying SYSAUX Space Consumers

It is a dynamic performance view in Oracle databases that provides information about the occupants (or components) and their space usage within the SYSAUX tablespace.

The SYSAUX tablespace is used to store data for various database components. Additionally, there is an excellent document on reducing SYSAUX tablespace occupancy due to fragmented tables and indexes, which is worth investigating.

This view can be useful for diagnosing issues related to SYSAUX tablespace growth, like the one we encountered. We can query this view to see which components are using the most space and determine whether any action is needed or not, such as moving components to other tablespaces or managing the space they use more effectively.

The root cause was the SYS.SCHEDULER$_JOB_OUTPUT table’s OUTPUT column. In our case, the table contained 25 million rows and was consuming 13.8 GB space.

I used the queries provided below to diagnose the issue.

SQL> SELECT schema_name, occupant_name, occupant_desc, space_usage_kbytes 
     FROM 
         v$sysaux_occupants
     ORDER BY 
        space_usage_kbytes desc  ;
SQL> SELECT owner,segment_name,segment_type,(bytes/1024/1024/1024) as GB_usage 
     FROM 
       dba_segments
     WHERE tablespace_name='SYSAUX' 
     ORDER BY 
      GB_usage desc;
SQL> SELECT * FROM dba_lobs WHERE segment_name = 'SYS_LOB0003495614C00003$$';

While diagnosing the issue, I learned that when the DBMS_OUTPUT.PUT_LINE command is run in a scheduler job, the output is directed to SCHEDULER$_JOB_OUTPUT table. In our case, the DBMS_OUTPUT.PUT_LINE command was called inside a loop that executed 6 million times whenever the job was triggered.

I informed the developer about the issue and removed the related line, which stopped the generation of new rows. To remove the old records, the DBMS_SCHEDULER.PURGE_LOG procedure should be used.

Purging Scheduler Logs and Reclaiming Space

This procedure purges rows from the job and window logs that were not automatically purged by the Scheduler.

By default, the Scheduler automatically purges all rows in the job and window logs that are older than 30 days. The PURGE_LOG procedure can be used to purge additional rows from these logs.

Rows in the job log table related to the steps of a chain are only purged when the entry for the main chain job is purged, either manually or automatically.

Purging the log does not immediately release the space. A TRUNCATE operation is required to lower the high water mark and reclaim the space.

Also there is a document in Oracle Support SYSAUX Tablespace Growing Due to SYS.SCHEDULER$_JOB_OUTPUT LOB Column (Doc ID 2095104.1 related with the issue.

SQL> begin
dbms_scheduler.purge_log(0,'JOB_AND_WINDOW_LOG');
end;
	
SQL> truncate table sys.SCHEDULER$_JOB_OUTPUT;
Table truncated.

Important: SYS.SCHEDULER$_JOB_OUTPUT is an Oracle-maintained internal table. Although directly truncating it reclaimed space in this particular environment, manually modifying or truncating SYS-owned internal tables should not be considered a standard maintenance procedure. Use supported Scheduler log-purging mechanisms where applicable, and consult Oracle Support before performing direct operations on internal tables.

The story so far we no so much exciting, but the surprising part is just about to unfold.

After truncating the table and removing the unwanted noisy data from the SCHEDULER$_JOB_OUTPUT table, I checked the table the next day to ensure no new records had been generated from a DBMS_OUTPUT command. Indeed, there were no records related to the scheduler jobs that had previously generated rows.

A Second Problem: ORA-01031 in Automatic Segment Advisor

However, a log record from an advisor task run of a maintenance job, stored in the SCHEDULER$_JOB_OUTPUT table, caught my attention.

SQL> SELECT * FROM sys.SCHEDULER$_JOB_OUTPUT;
LOG_ID 		ERRORS 			OUTPUT
2001716		HugeBlob 		HUGEBLOB
...
SQL> SELECT LOG_ID,LOG_DATE,NAME,OWNER, OPERATION, STATUS FROM  sys.scheduler$_event_log  WHERE log_id=2001716;
LOG_ID	LOG_DATE                            NAME                    OWNER. OPERATION	STATUS	
2001716	24/07/2021 07:02:43.194381 +03:00   ORA$AT_SA_SPC_SY_57806  SYS	   RUN	        SUCCEEDED
SQL> SELECT client_name,status FROM dba_autotask_client; 
CLIENT_NAME					STATUS
auto optimizer stats collection	                ENABLED
auto space advisor				ENABLED --->
sql tuning advisor				ENABLED													

This record (ORA$AT_SA_SPC_SY_57806 ) belongs to the auto space advisor task.

What is Automatic Segment (Space) Advisor ?

Automatic Segment Advisor identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.

You can also run the Segment Advisor manually to obtain more up-to-the-minute recommendations or to obtain recommendations on segments that the Automatic Segment Advisor did not examine for possible space reclamation.

The task status was recorded as ‘SUCCEEDED’ in the SYS.SCHEDULER$_EVENT_LOG table, and the errors column in SYS.SCHEDULER$_JOB_OUTPUT was null. There were no anomalies until this point. However, in the output column of the record, there was a significant number of errors, like the one provided below.

Owner :XXX Tabname :YYY Partname : Segtype :TABLE Tbsname :USERS

Exception in get_compressible_size code: -1031 msg: ORA-01031: Insufficient privileges

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1313

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1307

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1026

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 252

ORA-06512: at “SYS.DBMS_COMPRESSION”, line 250

ORA-06512: at line 1

Segment Information :

Owner :XXX Tabname :ZZZ Partname : Segtype :TABLE Tbsname :USERS

Exception in get_compressible_size code: -1031 msg: ORA-01031: Insufficient privileges

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1313

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1307

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1026

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 252

ORA-06512: at “SYS.DBMS_COMPRESSION”, line 250

ORA-06512: at line 1

Segment Information :

Owner :XXX Tabname :GGG Partname : Segtype :TABLE Tbsname :USERS

Exception in get_compressible_size code: -1031 msg: ORA-01031: Insufficient privileges

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1313

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1307

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1026

ORA-06512: at “SYS.PRVT_COMPRESSION”, line 252

ORA-06512: at “SYS.DBMS_COMPRESSION”, line 250

ORA-06512: at line

The Automatic Segment Advisor was encountering ORA-01031 errors while running. I searched Oracle Support for similar cases but found nothing relevant. This advisor can be triggered manually by executing the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure.

Investigating ORA-01031 with Oracle Tracing

To identify the root cause of the privilege error, I enabled 10046 tracing and the 1031 error stack, then executed the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure.

SYS@bltdb1> oradebug setmypid
Statement processed.
SYS@bltdb1> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/bltdb/bltdb1/trace/bltdb1_ora_268276.trc
SYS@bltdb1> alter system set events '1031 trace name errorstack level 3';
SYS@bltdb1> alter session set events '10046 trace name context forever, level 28';
SYS@bltdb1> exec dbms_space.auto_space_advisor_job_proc();

The procedure was attempting to create a temporary table behind the scenes, requiring the CREATE TABLE privilege to be granted directly to the user who owns the segment (table or index). The CREATE TABLE privilege inherited through the RESOURCE role was insufficient due to the difference in privilege contexts (INVOKER vs. DEFINER rights).

Resolving the Automatic Segment Advisor Privilege Error

The solution I applied was to grant the privilege explicitly using GRANT CREATE TABLE TO XXX. Another workaround is to disable the related advisor task, as shown in the command below.

-- DISABLING AUTOMATIC SEGMENT/SPACE ADVISOR
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/

Preventing Scheduler Output Accumulation with STORE_OUTPUT

Edited on November 6th, 2024:

There is also a blog post worth checking out by Connor McDonald about DBMS_OUTPUT and the scheduler. Additionally, there is a useful comment that provides a workaround to prevent the DBMS_OUTPUT.PUT_LINE command, when run in a scheduler job, from directing output to the SCHEDULER$_JOB_OUTPUT table and instead directing it to stdout.” With this workaround, you can control it in a granular way at the level of each scheduler job. You may refer to Database Administrator’s Guide for Oracle Database release 19c.

BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ”
,attribute => ‘STORE_OUTPUT’
,value => FALSE);
END;

Hope it helps.