📋Steps in this guide1/5

ORA-00054 resource busy and acquire with NOWAIT specified

ORA-00054 resource busy and acquire with NOWAIT specified 1. Simulation 2. Identify the session that has locked the resource 3. Solution 1. Simulation To simulate this error for learning purposes, do as below in 2 sessions Session 1 : [oracle@rac2 ~]$ sqlplus sh/sh; SQL> create table lock_table (a number); Table created. SQL> insert into lock_table … Continue reading ORA-00054 →

oracle clusteringintermediate

by OracleDba

15 views

Overview

1. Simulation To simulate this error for learning purposes, do as below in 2 sessions Session 1 :

Code/Command (click line numbers to comment):

[oracle@rac2 ~]$
sqlplus sh/sh;
SQL>
create table lock_table (a number);
Table created.

SQL>
insert into lock_table values(100);
1 row created.

SQL>
commit;
Commit complete.

SQL> 
SQL>
update lock_table set a=500;
1 row updated.

SQL>

---- Do not commit -----

SQL>
select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46 <----
SQL>

Section 2

Session 2: ORA-00054: resource busy and acquire with NOWAIT specified or timeout 2. Identify the session that has locked the resource

Code/Command (click line numbers to comment):

[oracle@rac2 ~]$
sqlplus sh/sh;
SQL>
declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/
2    3    4    5    6
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout
expired
ORA-06512: at line 4

SQL>

Section 3

-- OR -- Output

Code/Command (click line numbers to comment):

The below query helps to identify the session that has locked the resource that your session is trying to lock.
[oracle@rac2 ~]$
sqlplus / as sysdba
SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');
select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (46);
-- OR --
set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
  ,  s.sid || ',' || s.serial# "SID,SESSION#"
  ,  owner || '.' || object_name object
  ,  object_type
  ,  decode( l.block
     ,       0, 'Not Blocking'
     ,       1, 'Blocking'
     ,       2, 'Global') status
  ,  decode(v.locked_mode
    ,       0, 'None'
    ,       1, 'Null'
    ,       2, 'Row-S (SS)'
    ,       3, 'Row-X (SX)'
    ,       4, 'Share'
    ,       5, 'S/Row-X (SSX)'
    ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
 from       v$locked_object v
 ,  dba_objects d
 ,  v$lock l
 ,  v$session s
 where      v.object_id = d.object_id
 and        v.object_id = l.id1
 and        v.session_id = s.sid
 order by oracle_username,session_id;
To identify the locked rows, use the below query:
set lines 200
col object_name for a30
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from    v$session s
,   dba_objects do
where   s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('&OBJECT_NAME_WITHOUT_SCHEMA_NAME')
;
-- OR --
SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

SQL>
SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');
2    3    4

 OBJECT_ID SESSION_ID    INST_ID
---------- ---------- ----------
     82844
46
1
<--- Please note session ID 46
SQL>
select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (
46
);
2    3

       SID    SERIAL#    INST_ID PROGRAM                                          SPID    
---------- ---------- ---------- ------------------------------------------------ --------
46         11
1 [email protected] (TNS V1-V3)          5768

SQL>

Section 4

3. Solution You can try executing the query/job after some time (to allow the other session to complete). kill the session that has locked the resource Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

You can try executing the query/job after some time (to allow the other session to complete).
--- OR
----
kill the session that has locked the resource
Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL>
alter system kill session '46,11';
System altered.

SQL>


SQL> declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/  2    3    4    5    6
PL/SQL procedure successfully completed.
<-- executed without error after killing problematic session
SQL>

Section 5

Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!