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.

What does the error message "fork: retry: Resource temporarily unavailable"

Issue:

The following errors are seen in /var/log/secure:

Feb 23 14:34:57 erpuatappl sshd[11618]: fatal: setresuid 501: Resource temporarily unavailable
Feb 23 14:41:15 erpuatappl sshd[11850]: error: do_exec_pty: fork: Resource temporarily unavailable

Root Cause:
There can be various reasons for processes not being able to fork:

There is a misbehaving service or process running, consuming more resources than expected.
The system was not able to create new processes, because of the limits set for nproc in /etc/security/limits.conf.
The system ran out of memory and new processes were unable to start because they could not allocate memory.
There is not an available ID to assign to the new process. A unique value less than kernel.pid_max must be available.

Resolution:
There can be various reasons for processes not being able to fork and thus that means there are also various resolution:

1. When the system runs into a limitation in the number of processes, increase the nproc value in /etc/security/limits.conf or /etc/security/limits.d/90-nproc.conf depending on RHEL version. 

2. The limit can be increased for a specific user or all users. For example, here is an example of /etc/security/limits.d/90-nproc.conf file.

<user>       -          nproc     2048      <<<----[ Only for "<user>" user ]

*            -          nproc     2048      <<<----[ For all user's ]

3. Check the total number of threads and processes running on the server:

[applmgr@erpuatappl ~]$  ps -eLf | wc -l

2332

[applmgr@erpuatappl ~]$ cat /proc/sys/kernel/pid_max

32768

For example, if the above result is 2332, then increase kernel.pid_max to 32768.

kernel.pid_max must be larger than the total number of simultaneous threads and processes.

Diagnostic Steps:

Check with sar whether all memory was used or whether a large number of processes was spawned.

**In order to check the use of processes against what is allowed for the user, check the output of ulimit -u for the limit set to the particular user, and compare with the number of processes the user is runing.

**You can run the below command to find the number of processes opened for every user and compare if that limit is exceeded with what defined in /etc/security/limits.conf or /etc/security/limits.d/*.

[applmgr@erpuatappl ~]$ ps --no-headers auxwwwm | awk '$2 == "-" { print $1 }' | sort | uniq -c | sort -n
      1 dbus
      1 gdm
      1 rpc
      1 rpcuser
      2 postfix
      3 68
      3 rtkit
      5 mfe
    165 oracle

    813 applmgr
    317 root

**Increase the value for the "nproc" parameter in /etc/security/limits.conf.
Add the following settings to /etc/security/limits.conf:

oracle           soft     nproc   4096
oracle           hard    nproc   16384
applmgr        soft     nofile  4096
applmgr        hard    nofile  65536

                                           

**Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session     required     pam_limits.so

                                      OR

Make a profile if it does not already exist and then put below entry:

**Add the following lines to /etc/profile:

if [ $USER = "oracle" ]; then
    if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
fi