Oracle report ORA-00054 resources are busy solutions

From: http://www.cnblogs.com/loveLearning/p/3625544.html

Oracle Error: ORA-00054: The resource is busy and requires the specified NOWAIT

The problem is as follows:

Sql> Conn Scott/[email Protected]_database
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as Scott

。。。。。。

sql> drop table Student2;

drop table Student2

ORA-00054: The resource is busy, but the resource is specified as NOWAIT, or the timeout expires
=========================================================

Here's how to fix it:

=========================================================

Sql> select session_id from V$locked_object;

session_id
----------
142

sql> SELECT SID, Serial#, username, osuser from v$session where sid = 142;

SID serial# USERNAME Osuser
---------- ---------- ------------------------------ ------------------------------
142 SCOTT Lilwen

sql> ALTER SYSTEM KILL SESSION ' 142,38 ';

System Altered

1. Querying for locks in the database

SELECT * from V$lock;
SELECT * from V$lock where block=1;

2. Querying for Locked objects

SELECT * from V$locked_object;

3. Query blocking

Check for blocked sessions
SELECT * from V$lock where lmode=0 and type in (' TM ', ' TX ');

Check for blocking other session locks
SELECT * from V$lock where lmode>0 and type in (' TM ', ' TX ');

4. Querying the process that the database is waiting for a lock

SELECT * from v$session where lockwait are NOT null;

5. Query the relationship between session lock waits

Select A.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from V$lock A,v$lock b
where A.id1=b.id1 and A.id2=b.id2 and A.block=1 and b.block=0;

6. Query the lock wait event
SELECT * from v$session_wait where event= ' enqueue ';

Solution:
Select session_id from V$locked_object; --First get the session_id of the locked object
SELECT SID, Serial#, username, osuser from v$session where sid = session_id; --Get the SID and serial# of V$session through the session_id obtained above, then terminate the process.
ALTER SYSTEM KILL SESSION ' sid,serial ';
Example
ALTER SYSTEM KILL SESSION ' 13, 8 ';

Oracle report ORA-00054 resources are busy solutions