Compile Security Request Hangs and Causes Many Database Locks

Hi ,

Yesterday , One User was getting some error while accessing some function . The error was "The Function Is Not Available Under The Responsibility" . And for the solution , we refer to the metalink_doc : 454285.1 .

According to this doc , Compile security need to be run . Thus , we submitted the concurrent " COMPILE SECURITY " with parameter " YES " . But our concurrent request completed with the error . The error is as mentioned below ..

---------------------

**Starts**12-JUL-2008 16:03:24
ORACLE error 60 in FDPSTP

Cause: FDPSTP failed due to ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "APPS.FND_FUNCTION", line 1752
ORA-06512: at "APPS.FND_FUNCTION", line 1761
ORA-06512: at line 1

------------------------

But in turn it causes Causes Many Database Locks . Users were not able to login and while accessing some forms , error was coming like " ORA-00060: deadlock detected wh.." . This is because of the lock created on the table "FND_COMPILED_MENU_FUNCTIONS" .Some solution for this is mentioned in Metalink Doc:332801.1 . But , it was not possible for us to follow it ( as we works in Production enviorement ) .

Thus , what we did . We found the session accessing the table "FND_COMPILED_MENU_FUNCTIONS" . Sessions using form "OEXOEORD" were creating the maximum locking on the concerned table . We killed all the Active & Inactive session using this form . And after this we rebooted our AP Node ( form & apache server ) .

This worked for us and our database comes in normal stage after this ..


According to metalink Note : 314308.1 , Please read the mentioned doc for more information ..



Regds
Rahul Gupta