OEM13
Cleaning up JOB’s with erros in OEM 13c.
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

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

 

André Ontalba

 

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.”