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:
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
Paul
Olivier
Paul
Giuseppe