SQL>update emp set employee_name='xxxxx' where employee_no='1002';
ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired
This means that there is some lock on this object ( table ) . Finding that session and then killing the session will resolve the issue.
-- Finding the locks on the object of DML type
set linesize 150
COLUMN DT FORMAT A15
COLUMN USR FORMAT A10
col object_name format a25
col object format a30
COL SID FORMAT 9999
COL SERIAL# FORMAT 99999
COL PID FORMAT A10
SELECT 'alter system kill session'''||A.SID||','||A.SERIAL#||''';' KILLSTMT,
'kill -9 '||D.spid ,
A.SID,
a.serial#,A.INST_ID,
d.spid pid,
A.USERNAME USR,
-- c.object_name,
decode(b.type, 'MR', decode(a.username, null,
'DICTIONARY OBJECT', a.username||'.'||c.object_name),
'TD', c.object_name,
'TM', c.object_name,
'RW', 'FILE#='||substr(b.id1,1,3)||
' BLOCK#='||substr(b.id1,4,5)||' ROW='||b.id2,
'TX', 'RS+SLOT#'||b.id1||' WRP#'||b.id2,
'WL', 'REDO LOG FILE#='||b.id1,
'RT', 'THREAD='||b.id1,
'TS', decode(b.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||b.id1||' ID2='||b.id2) object,
decode(b.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||b.type) type,
LTRIM(RTRIM(TO_CHAR(A.LOGON_TIME,'DD/MM'))) DT,
B.CTIME,b.block
FROM GV$SESSION A,
GV$LOCK B,
dba_objects c,
Gv$process d
WHERE A.SID=B.SID AND
b.id1=c.object_id and
A.USERNAME IS NOT NULL AND
a.paddr=D.addr
and c.object_name='&OBJ'
order by A.USERNAME
/
SID SERIAL# PID USR OBJ
---------------------------------------------------------------------------------------------------------
1988 29 7076 TEST EMP DML/DATA ENQ 09/07
--- To kill the session
ALTER SYSTEM KILL SESSION '1988,29' IMMEDIATE;
Even then it is throwing the same error . The PID needs to be killed on O/S level
for Example in linux the following command need to be executed by connecting as oracle
$kill -9 7076 --- here 7076 is the pid listed above in locks query
Tags:
Oracle