R12: Unable to update an Invoice in Invoice Entry form (APXINWKB): APP-SQLAP-10771: could not reserve record.
ERROR
APP-SQLAP-10771: Could not reserve record
Got this error when trying to pay Full Invoice. This error comes normally due to database lock from another sessions on AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL tables.
SOLUTION
Step 1. First determine what session has lock this record, please execute the following steps:
a) Run the following to query to determine which tables are locked:
SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40) FROM v$locked_object a, dba_objects b WHERE a.object_id = b.object_id AND b.object_name like ‘AP_%’ ORDER BY b.object_name;
b) Look at the results and run the below given Query by inserting that is returned from above script :
SELECT l.*, o.owner object_owner, o.object_name FROM SYS.all_objects o, v$lock l WHERE l.TYPE = ‘TM’ AND o.object_id = l.id1 AND o.object_name in (‘AP_INVOICES_ALL’, ‘AP_INVOICE_LINES_ALL’, ‘AP_INVOICE_DISTRIBUTIONS_ALL’);
c) Now, run the below given query and enter the SID returned by above query.
SELECT SID, SERIAL# FROM v$session WHERE SID = <SID from Above Query)>;
Step 2. As the locking sessions have been identified, please use the below command to kill sessions.
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;
SID and serial# to be taken from the output of 1C.
-----END----
#################################################################################
Objects Locked in Module
SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.object_name like 'AP_%'
ORDER BY b.object_name;
Table wise Objects Locked
SELECT l.*, o.owner object_owner, o.object_name
FROM SYS.all_objects o, v$lock l
WHERE l.TYPE = 'TM'
AND o.object_id = l.id1
AND o.object_name in ('AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');
SELECT SID, SERIAL# FROM v$session WHERE SID in (306, 312);
ALTER SYSTEM KILL SESSION '312,322' IMMEDIATE;
-----------------------------------------------------------------------------------------------------------
Object Locked in User
selecta.object_id, a.session_id, substr(c.object_name, 1, 40), b.CLIENT_IDENTIFIER
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
All in One Query
select
o.owner,l.SID, b.SERIAL#,a.object_id, a.session_id, substr(c.object_name, 1, 40), b.CLIENT_IDENTIFIER, b.LOGON_TIME
from
v$locked_object a ,
v$session b,
dba_objects c,
SYS.all_objects o,
v$lock l
where
b.sid = a.session_id
and
a.object_id = c.object_id
AND o.object_id = l.id1
and o.object_id=a.OBJECT_ID
and o.object_id=c.object_id
and l.ID1=a.OBJECT_ID
and l.ID1=c.object_id
and l.TYPE = 'TM'
and b.SID=l.SID
order by b.LOGON_TIME asc ;
