Cleaning up JOB’s with OEM 13c errors
Today a simple but useful article, in OEM 13c we have the very useful job schedulling system.
However, as we can see in the image below when we have several errors, it is difficult to clean the jobs using the web interface or EMCLI.
EMCLI verb “get_jobs” reports all runs of the jobs, and “delete job” can delete job and its all runs but it’s not possible to delete a specific run of a job. For example, if you want to delete hundreds of “failed” (or “skipped”) runs of a job, EMCLI won’t help you and doing it through the web interface will be very time consuming.
We can easily solve this with a PL/SQL script to clean up jobs.
Connect to sqlplus with the sysman account:
[oracle@dbadutra:/home/oracle] sqlplus sysman@OEM13c SQL*Plus: Release 220.127.116.11.0 – Production on Fri 28 08:15:03 2020 Version 18.104.22.168.0 Connected to: Oracle Database 19c Enterprise Edition Release 22.214.171.124.0 – Production Version 126.96.36.199.0 SQL> BEGIN FOR C IN (SELECT SCHEDULED_TIME, JOB_ID FROM MGMT$JOB_EXECUTION_HISTORY WHERE JOB_OWNER = ‘SYSMAN’ AND JOB_ID IN (SELECT DISTINCT(JOB_ID) FROM MGMT$JOB_EXECUTION_HISTORY WHERE STATUS=’Error’ or STATUS=’Failed’) AND STATUS = ‘Error’ or STATUS=’Failed’) LOOP EM_JOB_OPS.DELETE_JOB_RUN(C.JOB_ID,C.SCHEDULED_TIME); END LOOP; COMMIT; END;
Now let’s get there and the jobs are clean
I hope I helped with this tip
Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”