Oracle Application Technology

Recently came across a typical scenario where I needed to cancel a long running concurrent request and while doing the same from the front end, I kept getting error “Could not lock the record“.

Later on I thought to mark it as terminated by running below “update” statement from the database.

UPDATE apps.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE request_id = 126192043
and status_code ='R' 
and  phase_code = 'R';

commit
/

But again this “update” was taking exceptionally long time. Then I figured out a better and quicker way of doing this.
Step:1 Find out the “FNDLIBR” process associated with the long running concurrent request by running below query

Set Pages 1000
Set head on
Column Manager   Format A12
Column Request   Format 999999999
Column Program   Format A30
Column User_Name Format A15
Column Started   Format A15
Column FNDLIBR  Format A9
prompt Managers that is running a request and FNDLIBR PROCESS;
select substr(Concurrent_Queue_Name, 1, 12) Manager,
       Request_Id Request,
       User_name,
       Fpro.OS_PROCESS_ID "FNDLIBR",
       substr(Concurrent_Program_Name, 1, 35) Program,
       Status_code,
       To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
  from apps.Fnd_Concurrent_Queues    Fcq,
       apps.Fnd_Concurrent_Requests  Fcr,
       apps.Fnd_Concurrent_Programs  Fcp,
       apps.Fnd_User                 Fu,
       apps.Fnd_Concurrent_Processes Fpro
 where Phase_Code = 'R' And Status_Code <> 'W' And
       Fcr.Controlling_Manager = Concurrent_Process_Id and
       (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id and
       Fcq.Application_Id = Fpro.Queue_Application_Id) and
       (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id and
       Fcr.Program_Application_Id = Fcp.Application_Id) and
       Fcr.Requested_By = User_Id and
       Fcr.request_id =&request_id
	   /

Step:- 2 Now look for the FNDLIBR process ID obtained above on the “Concurrent Manager Node”

ps -ef|grep 9240602|grep -v grep
  applmgr 19859 18919  0 Mar04 ?        00:00:02 FNDLIBR 

Step:- 3 Query the database to get the Session details for the offending process obtained in Step 2

select ses.sid,
              ses.serial# serial#,
              proc.spid,
              ses.sql_id,
              ses.process,
              ses.last_call_et,
              ses.event
         from gv$session ses, gv$process proc
        where ses.paddr = proc.addr and ses.process in ('&process_ID');

Step:- 4 Now clear the database session by running below statement in the database(Using SID and Serial# obtained in Step 3)

SQL> alter system kill session '<SID>,<Serial#>' immediate;
SQL>

Step:- 5 Finally go ahead and cancel the long running request either from the front end or from database (using update statement as mentioned in the beginning.

Hope this helps. Happy learning and keep reading.

-Anand M