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:
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:
so for example:
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:
Until you can do:
Now you can set:
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:
Now suppose that you cleaned up the job logging. After a few hours work, it's almost done. Just run:
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;