We are using Oracle Forms9i for application (backend: Oracle9i DB on SUN Solaris 8.0). During development time, we are ruuning our forms from the m/c where oracle9iDS is installed after starting OC4J instance. The sequence which we are following is given below:
1. Accessing a procedure <PROC_A> from Oracle Forms9i. The forms is being executed inside OC4J container from the machine where oracle9iDS is installed. (During Development Time).
2. Stopped the Form execution by closing the form & stopping OC4J instance.
3. Now, connecting to SQL*PLUS and trying to modify my procedure <PROC_A> for some reason. We are executing command CREATE or REPLACE PROCEDURE <PROC_A>.
4. At this point, SQL*PLUS hangs (Not Responding) and after 9-10 minutes, it terminates by giving error ORA-04021: time-out occurred while waiting to lock object <PROC_A>. It seems that form has locked procedure <PROC_A> and is still being used. And that's why we are not able to modify the procedure again.
5. I checked & can see the procedure <PROC_A> is still being used for my session in tables V$ACCESS & V$SESSION_WAIT. Oracle doc. says it is waiting for some client message to server foreground process...& goes in waiting state. After around an hour or so, it gets cleared. (PMON must have cleared this...).
6. But, when we create & access any procedure through SQL*PLUS and modify it, we do not face any issue. It is alright always and OK.
We would like to know the reason for this & solution to resolve the above issue. Is it to do something with oracle forms or oracel DB.? Pls. suggest.