DBMS_SCHEDULER is occupying all of my SYSAUX tablespace. I ran
dbms_scheduler.purge_log which deleted 100 million rows from
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
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; /
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?