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 22.214.171.124.0 – Production on Fri 28 08:15:03 2020 Version 126.96.36.199.0 Connected to: Oracle Database 19c Enterprise Edition Release 188.8.131.52.0 – Production Version 184.108.40.206.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.”
André Luiz Dutra Ontalba é um Oracle ACE member, formado em Ciências da Computação, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Engineered Systems, Performance & Tuning, RAC, Oracle Cloud e Oracle ERP’s System; Trabalha com Oracle há 17 anos, certificado OCP Oracle 11/12g/Cloud e conta com mais de 27 outras certificações em produtos da Oracle. Atualmente trabalha como Senior Database Architect na Sogeti Luxembourg uma empresa da Capgemini Group. André é fundador do Grupo de Usuários Oracle de Luxemburgo (LUXOUG). Articulista para o OTN, GPO (Grupo de Usuários Oracle Brasil) e LUXOUG. Twitter @aontalba / blog www.dbadutra.com