ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX; JOB_SCHEDULER is biggest occupant

Question

DBMS_SCHEDULER is occupying all of my SYSAUX tablespace. I ran dbms_scheduler.purge_log which deleted 100 million rows from dba_scheduler_job_run_details, however v$sysaux_occupants and the data file size remain unchanged. Is there some additional action I need to take to clear the SYSAUX tablespace of DBMS_SCHEDULER generated data?

---

Attempting to insert a single row into a newly created table in my regular tablespace fails with:

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

I can see that JOB_SCHEDULER is taking up 92.4% of the SYSAUX tablespace by querying v$sysaux_occupants:

SELECT
    occupant_name,                    
    round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants                  
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;

I originally had over 100 million rows in dba_scheduler_job_run_details.

Yesterday, I ran the purge command (which took 3.5 hours):

BEGIN
    dbms_scheduler.purge_log;
END;
/

Today, dba_scheduler_job_run_details has less than 1K rows.

However, the query on v$sysaux_occupants is unchanged; today it still says JOB_SCHEDULER is occupying 92.4%. Likewise querying my data file sizes show that SYSAUX is still maxed out:

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
    v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

It seems like I have not actually deleted the space consumed by DBMS_SCHEDULER.

Is there some step I am missing to clean up all the space consumed by DBMS_SCHEDULER?

Answer 1

To release space to SYSAUX tablespace you can try:

alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space;
alter table sys.scheduler$_event_log disable row movement;

Answer 2

truncate table sys.scheduler$_event_log drop storage;