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 19.0.0.0.0 – Production on Fri 28 08:15:03 2020
Version 19.3.0.0.0
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.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.”
