Error: FRM-40501: ORACLE error: unable to reserve record for update or delete

This error message appears when the user tries to update a record in oracle e-business suite forms screen. Most of us knows the reason behind is that the same record is being updated by another user and the modifications have not been saved yet.

The below SQL can be used to list the details of the session which has locked any database object. You should be able to find the details of your record in this list.

WITH sessions_inline AS
 (SELECT client_identifier user_name
        ,sid seesion_id,SERIAL# serials#,(SELECT responsibility_name
          FROM   fnd_responsibility_vl
          WHERE  responsibility_key = SUBSTR(action
                                            ,INSTR(action 
                                                  ,'/'
                                                  ,1
                                                  ,1) + 1)) responsibility
        ,DECODE(SUBSTR(module
                      ,INSTR(module
                            ,':'
                            ,1
                            ,2) + 1
                      ,INSTR(module
                            ,':'
                            ,1
                            ,3) - INSTR(module
                                       ,':'
                                       ,1
                                       ,2) - 1)
               ,'frm'
               ,'Forms Screen'
               ,'wf'
               ,'Workflow'
               ,'cp'
               ,'Concurrent Process'
               ,'fwk'
               ,'Self Service Page'
               ,'bes'
               ,'Business Event'
               ,'gsm'
               ,'Workflow') usage_type
               ,SUBSTR(module
               ,INSTR(module
                     ,':'
                     ,1
                     ,3) + 1) ebs_module, ses.*
  FROM   v$session ses
  WHERE  1 = 1)
SELECT seesion_id
      ,SERIALS#
      ,obj.object_name
      ,user_name
      ,responsibility
      ,usage_type
      ,ebs_module
      ,DECODE(usage_type
             ,'Forms Screen'
             ,DECODE(ebs_module
                    ,'FNDSCSGN'
                    ,'EBS Navigator'
                    ,(SELECT user_form_name FROM fnd_form_vl WHERE form_name = ebs_module))) forms_screen
      ,DECODE(usage_type
             ,'Concurrent Process'
             ,(SELECT user_concurrent_program_name FROM fnd_concurrent_programs_vl WHERE concurrent_program_name = ebs_module)) concurrent_program
      ,DECODE(usage_type
             ,'Concurrent Process'
             ,(SELECT user_concurrent_queue_name FROM fnd_concurrent_queues_vl WHERE concurrent_queue_name = ebs_module)) concurrent_manager
FROM   sessions_inline ses
      ,v$locked_object lck
      ,all_objects     obj
WHERE  lck.session_id = ses.sid
AND    obj.object_id = lck.object_id
ORDER  BY ses.usage_type;

Above Query give the details object level lock. so we can run below query for checking next level.

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where b.sid = a.session_id

and a.object_id = c.object_id

and c.object_name='CE_SECURITY_PROFILES_GT';

In my case i am going to kill below lock.

SQL> alter system kill session '1144,10881';

System altered.

Issue resolved check and try again.