EM13c: Is It Possible to Delete a Job Run with EMCLI?

Enterprise Manager Cloud Control provides a centralized job scheduling system and it’s possible to control it through both web interface and EMCLI. On the other hand, it seems web interface provides more control over the past jobs, for example it’s not possible to delete a job run with EMCLI, while it’s possible to do it on web interface:

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.

So what is the solution? We need to make our hands dirty and call EM_JOB_OPS.delete_job_run procedure directly on the repository database.

Here’s a simple PL/SQL script to clean up failed jobs of a user:

BEGIN
  FOR c IN
  (SELECT scheduled_time
  FROM MGMT$JOB_EXECUTION_HISTORY
  WHERE job_owner=:1
  AND job_id     =:2
  AND status     ='Failed')
  LOOP
    EM_JOB_OPS.delete_job_run( :2, c.scheduled_time );
  END LOOP;
  COMMIT;
END;

BEGIN

FOR c IN

(SELECT scheduled_time

FROM MGMT$JOB_EXECUTION_HISTORY

WHERE job_owner=:1

AND job_id =:2

AND status ='Failed')

LOOP

EM_JOB_OPS.delete_job_run( :2, c.scheduled_time );

END LOOP;

COMMIT;

END;

Login to the Enterprise Manager repository database as SYSMAN, enter “job_owner” and “job_id” as bind variables. It will delete all failed runs of the job. You can query MGMT$JOB_EXECUTION_HISTORY view or use “emcli get_jobs” to learn the job owner and job ID information.

Please share

4 Comments

Paul

March 8, 2017 Reply

Thank you! very helpful in cleanup of several hundreds of skipped jobs.
Olivier

July 3, 2017 Reply

Good one, it was very helpful Thx
Paul

January 30, 2018 Reply

I use it often now, but sometimes I get:
ERROR at line 1:
ORA-20403: The specified job does not exist or is not viewable
ORA-06512: at “SYSMAN.EM_JOB_OPS”, line 2107
ORA-06512: at line 9

would you have any ideas on how to investigate this?
Giuseppe

June 10, 2022 Reply

Hi , try to add “distinct”
Example:
BEGIN
FOR c IN
(SELECT distinct job_id,scheduled_time
FROM MGMT$JOB_EXECUTION_HISTORY
WHERE 1=1
and status=’Failed’
and job_name like ‘%_ABC_%’
and scheduled_time<sysdate-10
)
LOOP
EM_JOB_OPS.delete_job_run( c.job_id, c.scheduled_time );
END LOOP;
COMMIT;
END;

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.