TriniDBA

Problem: When trying to add lines to a Standard RFQ (Request for Quotation), the user encounters the following error:

Could not reserve record [2 tries].

Solution: Kill lock on session.

History of Purchase Requisition was as follows:

Preparer submitted
Preparer forwarded
Approver approved
Buyer rejected
Preparer submitted
Preparer forwarded
Approver approved
Buyer approved
Buyer reserved

The buyer rejected the PR because the location had to be changed.

However, the PR lines appeared to be doubled (lines 1 to 16 were duplicated to form lines 17 to 32).

On the RFQ, 9 lines were added. When the RFQ was opened again to add the remaining 7, the following error appeared on the form:

Could not reserve record [2 tries].

The RFQ could not be updated.

In SQL Developer, the following query was run:

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;

It showed that the buyer had a row exclusive lock on the record.

The above query returned the SID and SERIAL# of the session locking the record. After sourcing the database environment, the sessions were checked and the lock was cleared using the following commands:

SELECT sid, serial#, username FROM v$session order by 1, 2, 3;
SELECT * FROM v$session order by sid, serial#, username;
SELECT * FROM v$session where sid in (414, 443) order by sid, serial#, username;
alter system kill session '414, 17187';

Did not need to kill session 443 as it automatically disappeared.

Reference:

http://www.bluegecko.net/oracle/frm-40501-could-not-reserve-record-2-tries-oracle-apps-record-locking/

[amazon asin=0070077290&template=iframe image&chan=default]    [amazon asin=1453742735&template=iframe image&chan=default]    [amazon asin=0615238440&template=iframe image&chan=default]