GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

Cleaning up JOB’s with OEM 13c errors

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

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.