How to clean up after ORA-27477

On a 2-node RAC:

WITH a AS (SELECT SYS.DBMS_QOPATCH.get_opatch_bugs bugs_output FROM DUAL)
SELECT *
FROM a,
XMLTABLE('bugInfo/bugs/*'
PASSING a.bugs_output
COLUMNS
bug_id number path '@id'
) x;

This sql produces a list of patched bugs. At times, it fails with ORA-27477.

To collect the patch information, a scheduler job is created on each of the RAC nodes, which extracts the needed information from the inventory. In order to do this, one scheduler job per RAC node is created. If there are any jobs left over from previous execution, this may fail because of the resulting name conflict, producing ORA-27477.

The solution is to clean up those jobs and re-execute.

select owner, job_name 
from dba_scheduler_jobs 
where job_name like 'LOAD_OPATCH_INVENTORY_%';

This will list the jobs which could not be redefined. Now drop the jobs and retry.

exec DBMS_SCHEDULER.DROP_JOB('LOAD_OPATCH_INVENTORY_1');
exec DBMS_SCHEDULER.DROP_JOB('LOAD_OPATCH_INVENTORY_2');
delete from opatch_inst_job;

commit;