Posts Tagged ‘FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]; ORACLE APPS RECORD-LOCKING’

Forms : Could not reserve record (2 tries). Keep trying

August 5th, 2015, posted in Oracle Queries
Share

Second Way : 

Locking And Unlocking Tables In Oracle,Locking Tables In Oracle,Unlocking Tables In Oracle,Tables In Oracle,Oracle DBA,DBA,APPS DBA,ORA DBA,Application DBA,Database DBA,Oracle Tricks,Locking Query in Oracle,Unlocking Query in DBA,Oracle Forms Error,Oracle Application Forms Error,Forms : Could not reserve record (2 tries). Keep trying,FRM-40501,FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]; ORACLE APPS RECORD-LOCKING

One of your end users attempts to modify a record, and the user is prompted with a dialog box asking “Could not reserve record [2 tries]. Keep trying?” The user answers “yes” and after being prompted a few more times the user gives up. Ultimately the user gets a “FRM-40501: ORACLE error: unable to reserve record for update or delete.” When this happens it’s nice to have some scripts ready to go to quickly diagnose what is causing the contention, so appropriate action can be taken. The forms don’t wait to the obtain the lock for very long, so it’s a little more difficult to determine the blocking lock. This first script provides a listing of the possible locks and some relevant E-Business Suite information for digging further.

 

Query 1:

select vs.audsid audsid,
 locks.sid sid,
 vs.serial# serial#,
 vs.username oracle_user,
 vs.osuser os_user,
 vs.program program,
 vs.module module,
 vs.action action,
 vs.process process,
 decode(locks.lmode,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_held,
 decode(locks.request,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_requested,
 decode(locks.type,
       'MR', 'Media Recovery',
       'RT', 'Redo Thread',
       'UN', 'User Name',
       'TX', 'Transaction',
       'TM', 'DML',
       'UL', 'PL/SQL User Lock',
       'DX', 'Distributed Xaction',
       'CF', 'Control File',
       'IS', 'Instance State',
       'FS', 'File Set',
       'IR', 'Instance Recovery',
       'ST', 'Disk Space Transaction',
       'TS', 'Temp Segment',
       'IV', 'Library Cache Invalidation',
       'LS', 'Log Start or Log Switch',
       'RW', 'Row Wait',
       'SQ', 'Sequence Number',
       'TE', 'Extend Table',
       'TT', 'Temp Table',
       locks.type) lock_type,
 objs.owner object_owner,
 objs.object_name object_name,
 objs.object_type object_type,
 round( locks.ctime/60, 2 ) lock_time_in_minutes
from v$session vs,
         v$lock locks,
         dba_objects objs,
         dba_tables tbls
where locks.id1 = objs.object_id
 and vs.sid = locks.sid
 and objs.owner = tbls.owner
 and objs.object_name =  tbls.table_name
 and objs.owner != 'SYS'
 and locks.type = 'TM'
 order by lock_time_in_minutes;

To determine the table(s) the form is trying to lock, use the Help->Record History menu option; this provides the base table or view for the form block.

Look through the result set from Query 1 for an object_name (typically a table or view) in the same vicinity as your table or view. If you have a form block based on a view, it may be helpful to look up the tables behind the view. If the action starts with an ‘FRM:%’, then another forms session has the lock.

One thing to note: You’ll often see the same user blocking themselves. This could be a training issue, or it could be due to a previous forms session that crashed, but the f60webmx process did not die. If this is the case, you can kill the application server OS process (based on the process value in Query 1).

Query 2 provides further details for results in Query 1 that are forms sessions – simply plop in the AUDSID from Query 1.

Query 2:

SELECT
        F.AUDSID,
        S.SID,
        S.SERIAL#,
        L.USER_ID,
        L.TERMINAL_ID,
        L.LOGIN_NAME,
        R.RESP_APPL_ID,
        R.RESPONSIBILITY_ID,
        F.FORM_ID,
        F.FORM_APPL_ID,
        L.PID,
        L.PROCESS_SPID,
        NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
        USR.USER_NAME,
        a.application_name,
        RSP.RESPONSIBILITY_NAME,
        FRM.USER_FORM_NAME,
        s.program,
        s.action,
        s.module,
        s.state,
        s.event,
        s.wait_class,
        s.seconds_in_wait
FROM FND_RESPONSIBILITY_TL RSP,
        FND_FORM_TL FRM,
        FND_USER USR,
        FND_LOGINS L,
        FND_LOGIN_RESPONSIBILITIES R,
        FND_LOGIN_RESP_FORMS F,
        GV$SESSION S,
        fnd_application_tl A
WHERE F.AUDSID = &ENTER_FORM_AUDSID
AND R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND RSP.LANGUAGE = 'US'
AND RSP.application_id = a.application_id
AND a.language = 'US'
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND FRM.LANGUAGE = 'US'
AND F.AUDSID = S.AUDSID;

If a concurrent program holds the lock, Query 3 provides a bit more information. Here we can see the user, concurrent program, how long it’s been running and log/output files.

Query 3:

select fcr.request_id,
         fcr.requested_by,
         fu.user_name,
         fcr.program_application_id,
         fcr.concurrent_program_id,
         fcr.actual_start_date,
         fat.application_name,
         fcp.concurrent_program_name,
         fcpt.user_concurrent_program_name,
         fcr.description,
         fcr.logfile_node_name,
         fcr.outfile_name,
         fcr.logfile_name,
         fcr.completion_text,
         fcr.parent_request_id,
         vs.process,
         vs.state,
         vs.event,
         vs.wait_class,
         vs.seconds_in_wait
         from v$session vs,
              fnd_concurrent_requests fcr,
              fnd_application_tl fat,
              fnd_concurrent_programs fcp,
              fnd_concurrent_programs_tl fcpt,
              fnd_user fu
         where vs.audsid =  &ENTER_CONC_PROCESS_AUDSID
         and vs.process = fcr.os_process_id
         and fcr.actual_completion_date is null
         and fcr.program_application_id = fat.application_id
         and fcr.program_application_id = fcp.application_id
         and fcr.concurrent_program_id = fcp.concurrent_program_id
         and fcr.program_application_id = fcpt.application_id
         and fcr.concurrent_program_id = fcpt.concurrent_program_id
         and fcr.requested_by = fu.user_id;

This should be enough information to chase down the offender (someone out for coffee and not save that latest change first?) or possibly even point to a process that needs attention.

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/
Also Check : http://aliimmam.com/lock-tables-and-unlock-tables-syntax/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well. Source : http://dwhlaureate.blogspot.com/2014/07/how-to-unlock-locked-table-in-oracle.html

*********************************************************************************************************************

Share

Locking And Unlocking Tables In Oracle

August 4th, 2015, posted in Oracle Queries
Share

Locking And Unlocking Tables In Oracle,Locking Tables In Oracle,Unlocking Tables In Oracle,Tables In Oracle,Oracle DBA,DBA,APPS DBA,ORA DBA,Application DBA,Database DBA,Oracle Tricks,Locking Query in Oracle,Unlocking Query in DBA,Oracle Forms Error,Oracle Application Forms Error,Forms : Could not reserve record (2 tries). Keep trying,FRM-40501,FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]; ORACLE APPS RECORD-LOCKING

One Way : 

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that ‘EMP’ table is locked,

 

SELECT object_id FROM dba_objects WHERE object_name='EMP';
 OBJECT_ID
----------
   7401242

If there are no locks present for the table ‘EMP’ this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242
SID
----------
3434

 

SELECT sid, serial# from v$session where sid=3434
SID        SERIAL#
---------- ----------
3434       92193

 

ALTER SYSTEM KILL SESSION '3434,92193' ;

 

Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/forms-could-not-reserve-record-2-tries-keep-trying/
Also Check : http://aliimmam.com/lock-tables-and-unlock-tables-syntax/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well. Source : http://www.bluegecko.net/oracle/frm-40501-could-not-reserve-record-2-tries-oracle-apps-record-locking/
*********************************************************************************************************************
Share

LOCK TABLES and UNLOCK TABLES Syntax For ORACLE

December 11th, 2013, posted in Oracle Queries
Share
Identify locked objects :

select object_name, v.session_id SID, v.oracle_username,type,lmode,request
       from v$locked_object v, v$lock l, dba_objects o
       where l.sid=v.session_id
       and v.object_id=o.object_id
       and l.block > 0;

Identify who locks whom :

select
      (select osuser from v$session where sid=a.sid) blocker,
      a.sid,
      (select serial# from v$session where sid=a.sid) serial#,
      ' blocks ',
      (select osuser from v$session where sid=b.sid) blockee,
      b.sid, c.username username
    from v$lock a, v$lock b, v$session c
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    and b.sid = c.sid

Kill session :

SQL> alter system kill session 'sid,serial#';

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/
Also Check : http://aliimmam.com/forms-could-not-reserve-record-2-tries-keep-trying/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well.

*********************************************************************************************************************

Share