While applying EBS patch "HRMS Release Update Pack 9 for Release 12.1 Patch 21980909", we find that the patch application will always stall at about two hours into the adpatch run because it is trying to recreate the package HR_SECURITY. This object is locked by a query being run by a discoverer related process.
The sql_text of the process holding the lock always looks similar to this:
select sql_text
from v$sqlarea
where sql_id ='5y2zs1fg0d49x';
SQL_TEXT
-------------------------------------
SELECT * FROM EUL5_B151109093718Q1V1
We can use "alter system kill session xxx", but I am concerned that this lacks elegance. Also, if we kill this and do not restart the patch quickly enough then HR_SECURITY will be locked again by another, very similar Discoverer EUL process. I suspect these are coming from dba_jobs which is loaded with several jobs for "EUL5_BATCH_PACKAGExxx.RUN" (where xxx is some number). We can use exec dbms_job.broken() to stop these, but I have doubts that this is enough. There is probably some nice way to do this from Discoverer, but I'm not familiar enough with this product to know what that navigation would be.
Can somehow who is familiar with Disco explain to me how to make it stop submitting jobs for awhile while I apply a patch?
-Dan