Tidy Job Scheduler Data

Got a problem I’m not quite sure is easily rectified.

I’ve got a 19c database that has a large SYSAUX tablespace – almost 27GB! When I looked what was causing the size there were two elements. Space Advisor and Job Scheduler.

set lines 120

col occupant_name format a30

select occupant_name,space_usage_kbytes

from v$sysaux_occupants

order by space_usage_kbytes desc;

OCCUPANT_NAME                 SPACE_USAGE_KBYTES

------------------------------ ------------------

SM/ADVISOR                              12591680

JOB_SCHEDULER                           11271744

SM/OPTSTAT                               1064640

XDB                                       724032

The former I’ve sorted be dropping and recreating the job with a 10 day retention rather than 30.

The database does use dbms_scheduler extensively, and the purging job does appear to be working – there was no records older than 30 days. So I made the decision to decrease the retention to 15. I was able to manually reduce the history to 15 days by incremental purging. How can I now claim the space back by the deleted records?