0

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?

  • What is the output of select sum(bytes/1024/1024) as mb from dba_segments where lower(segment_name)='scheduler$_event_log'; ? – pifor May 9 '20 at 17:06
  • @pifor The output is 9630, but a select count(1) from scheduler$_event_log is returning only 1K rows as of today. It seems to me that the value in dba_segments has not been refreshed after running dbms_scheduler.purge or something... – Matthew Moisen May 9 '20 at 17:11
  • I assume that purging with DBMS_SCHEDULER.PURGE_LOG only runs DELETE that does not release space to tablespace. – pifor May 9 '20 at 17:15
  • Please prove that newly created table is NOT is SYSAUX tablespace. SEGMENT CREATION DEFERRED can allow you to create a table where you shouldn't (like SYSAUX) but throw this error when you try to INSERT – Michael Kutz May 9 '20 at 20:08
  • @MichaelKutz the output of select tablespace_name where table_name = 'test_error' shows that the tablespace is not SYSAUX but instead it is the default tablespace for this schema. – Matthew Moisen May 9 '20 at 22:02
2

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;
  • After running this, the JOB_SCHEDULER percentage in v$sysaux_occupants dropped only slightly, from 92.4% to 88.33%. Your query on dba_segments dropped from 9630MB to ~1MB. Interestingly, the entry for SYSAUX in dba_data_files is still maxed out. I am able to create a table and insert a row successfully now, however I'm curious as to why v$sysaux_occupants and dba_data_files don't seem to have registered a big drop. Any ideas? Thanks! – Matthew Moisen May 9 '20 at 17:36
  • If you release space in a segment like table or index, the free space is released to the tablespace datafiles but the datafiles don't shrink unless you resize them manually. What is the total size of SYSAUX tablespace ? Could you post output of select occupant_name, SCHEMA_NAME, SPACE_USAGE_KBYTES/1024 as mb from v$sysaux_occupants order by 3 desc ? – pifor May 9 '20 at 17:52
  • Interesting to know that there is also a bug in many Oracle version lower than 18.1: Bug 10404641 $sysaux_occupants.space_usage_kbytes may not be accurate. To get total size of objects in SYSAUX it's better to use: select sum(bytes/1024/1024) as mb from dba_segments where tablespace_name='SYSAUX'; – pifor May 9 '20 at 18:09
  • The output of your query on v$sysaux_occupants is (JOB_SCHEDULER, SYS, 15872). In `dba_data_files, the bytes and maxbytes for SYSAUX are both 32767.984375 MB. The output of your query on dba_segments is 23317.9375 MB. – Matthew Moisen May 9 '20 at 21:56
-1
truncate table sys.scheduler$_event_log drop storage;
  • 1
    On a sys. table? No – Philᵀᴹ May 9 '20 at 19:57
  • why not? it's simple LOG. lol – Needle file May 10 '20 at 5:37

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.