Sometimes that when user complaining that they hit with error on “Record is currently being worked on by another user, Please try to update it later”. One of the possibility was due to Oracle table locking.
Another possible problem was the user session was waiting for another user session to complete
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session WHERE state IN ('WAITING') AND wait_class != 'Idle'
AND event LIKE '%enq%'
SID : 123
USERNAME : APPS
EVENT : enq: TX – row lock contention
BLOCKING_SESSION : 254
SECONDS_IN_WAIT : 8300
WAIT_TIME : 0
Looking at this SQL output, immediately we concludes that APPS’s session with SID 123 is waiting for a lock on a table and that that lock is held by SID 254 (BLOCKING_SESSION).
To check what is running on both SID 123 and SID 254, we can find using the script below: –
--Active Session Info
SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser,
b.machine, b.STATUS,b.logon_time, c.sql_text
FROM v$process a, v$session b, v$sqltext c
AND c.hash_value = b.sql_hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid IN ('123', '254')
ORDER BY a.spid, c.piece
We can check this using OEM function “BLOCKING SESSION” as well, but this SQL script will come in handy if the OEM was not accessible.
Next» Cannot Unlink: Permission Denied