SOLVED

ORA-00054: Resource Busy And Acquire With NOWAIT Specified

Asked by OracleDba••13 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
ORA-00054: Resource Busy And Acquire With NOWAIT Specified

PROBLEM:

SQL> create index IND_TEST on DBATEST ( OBJECT_ID);

create index IND_TEST on DBATEST ( OBJECT_ID)

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
There are some other sessions which are blocking this sessions

1. Check for locked_objects

select lo.session_id,lo.oracle_username,lo.os_user_name,

lo.process,do.object_name,

decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',

3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',

to_char(lo.locked_mode)) mode_held

from v$locked_object lo, dba_objects do

where lo.object_id = do.object_id

order by 1,5

/

Client

Oracle OS Process Table

Sid Username Username ID Locked MODE_HELD

----- ---------- ---------- ------------------------ ------------------------------ ---------------

271 DBACLASS oracle 3323 DBATEST Row Excl (SX)

We can see, there is an exclusive lock on the table.

Kill the session, to release the lock

SQL> select sid,serial#,username,status from v$session where sid=271;

SID SERIAL# USERNAME STATUS

---------- ---------- ------------------------------ --------

271 1409 DBACLASS INACTIVE

SQL> alter system kill session '271,1409' immediate;

System altered.

Now re-run the create index statement,

SQL> create index IND_TEST on DBATEST ( OBJECT_ID);

Index created.

Hope it Helps!
OracleDba•

Post Your Solution