INTRO – SCHEDULER$_JOB_OUTPUT table was consuming 14 GB space.
Recently, I was asked for help with a database experiencing an unexpected growth issue in the SYSAUX tablespace. For troubleshooting space usage issues in the SYSAUX tablespace, V$SYSAUX_OCCUPANTS is the primary reference.
What is V$SYSAUX_OCCUPANTS view?
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.
DBMS_SCHEDULER.PURGE_LOG procedure
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.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. 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.
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).
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;
/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 => TRUE);
END;Hope it helps.
Osman’s DBlog 
Leave your comment