dbms_scheduler log history - purging manually

Today, I came across a SYSAUX tablespace that was asking for more space. It was about 12Gb, which seems a bit large to me. If you use the $ORACLE_HOME/rdbms/admin/awrinfo.sql , you will get a report of the occupants.  Occupant JOB_SCHEDULER took over 11Gb space, which makes you think.

The default out-of-the-box maintainance job, uses the global attribute LOG_HISTORY to remove old dbms_scheduler job logging:

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

Now this is default 30 days, and can be set with:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','15');

But what if your maintainance jobs are failing for some reason (timeout/bugs?). You may need to clean it manually. The easy way to do this, is to use:

exec DBMS_SCHEDULER.PURGE_LOG(<days>, which_log=>'JOB_LOG');

so for example:

exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>'JOB_LOG');

But if the number of logs is very large, you have to do this carefully, step by step:

First determine the distribution of logging you have.

Select count(1) from dba_scheduler_job_log where log_date < sysdate – 100;

Make sure that you get an idea like:

older than 300 days	12 rows
older than 250 days	12831 rows
older than 200 days	438121 rows

Now step by step clean it, by 5 or days at a time:

exec DBMS_SCHEDULER.PURGE_LOG(150,which_log=>'JOB_LOG');
exec DBMS_SCHEDULER.PURGE_LOG(140,which_log=>'JOB_LOG');
etc.

Until you can do:

exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>'JOB_LOG');

Now you can set:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','15');

and watch your maintainance job (PURGE_LOG) over the next days.

What if your PURGE_LOG job doesn't run? I had this, and a simple disable/enable was needed to get the right NEXT_RUN_DATE in dba_scheduler_jobs:

exec dbms_scheduler.disable('PURGE_LOG');
exec dbms_scheduler.enable('PURGE_LOG');

Now suppose that you cleaned up the job logging. After a few hours work, it's almost done. Just run:

exec DBMS_SCHEDULER.PURGE_LOG();

Which should be fairly quick (maybe 2 minutes) now.

The tables need  a shrink to release the space in the SYSAUX tablespace.

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

alter table sys.scheduler$_job_run_details enable row movement;
alter table sys.scheduler$_job_run_details space cascade;