Some times you get the error message as “The record cannot be reserved for update. It has already been reserved by another user” or “Could not reserve record(2 tries). Keep trying?” While querying/updating the Purchase Order/Sales Order/any other transaction/setup screen in the Oracle EBS or Oracle DB related custom applications.
Generally you get this message when second user is trying to open/update the same transaction when other user is already opened/updated the transaction, but not yet saved the changed and closed the screen. Some times user1 opens the transaction and leave the session as it is without any activity on this transaction. When the second user is trying to open the same transaction, then also the second user gets this error.
Technical Reason: Once the user opened the transaction and made some change(s), then the record will be locked at the database level(Row Level locking). Until the lock is released in the database, No other user can make changes to this record. Some times database locks are released automatically after some time. To release the locks incase of emergency situations, We can contact the dba team to find out who opened the transaction and to release the locks.
DBA is going to check the locks on the object(s), then kill the inactive session to release the lock(s).
The locks can be find out using the below Select statements if the object has any session locks. The objects are like Purchase Orders (PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL), Sales Order (OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, OE_PRICE_ADJUSTMENTS_ALL) etc.,
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 Object_name IN (‘PO_HEADERS_ALL’,’PO_LINES_ALL’,’PO_LINE_LOCATIONS_ALL’,’PO_DISTRIBUTIONS_ALL’)
Once the session’s SID and Serial# are figured out, then DBA/authorized database user can kill the inactive session using ALTER SYSTEM Kill Session coammnd.
ALTER SYSTEM KILL SESSION ‘sid,serial#’;
like ALTER SYSTEM KILL SESSION ‘1141,417’;
Once the session lock is released, The record can be opened and updated by another user.
Releasing locks on the database packages:
Below SELECT statement helps to find out the lock on the database packages.
SELECT * FROM DBA_DDL_LOCKS WHERE name like ‘PO_MOAC_UTILS_PVT%’
SELECT * FROM V$ACCESS WHERE OBJECT=’PO_MOAC_UTILS_PVT’
SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (1012,1135)
once you know the sid, serial# info, you can kill the session(s) using ALTER SYSTEM KILL SESSION command. The package can’t be migrated when the database package has locks.
First you have to understand & analyze the risks associated with killing session prior to executing the ALTER SYSTEM Kill Session command. You will loose all the unsaved changes and kicks out the related session users from the applications.