oracle加索引资源正忙,Oracle报 ORA-00054资源正忙的解决办法

来源于：http://www.cnblogs.com/loveLearning/p/3625544.html

oracle之报错:ORA-00054: 资源正忙，要求指定 NOWAIT

问题如下：

SQL> conn [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: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

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

解决方法如下：

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

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 38 SCOTT LILWEN

SQL> ALTER SYSTEM KILL SESSION ‘142,38‘;

System altered

1.查询数据库中的锁

select * from v$lock;

select * from v$lock where block=1;

2.查询被锁的对象

select * from v$locked_object;

3.查询阻塞

查被阻塞的会话

select * from v$lock where lmode=0 and type in (‘TM‘,‘TX‘);

查阻塞别的会话锁

select * from v$lock where lmode>0 and type in (‘TM‘,‘TX‘);

4.查询数据库正在等待锁的进程

select * from v$session where lockwait is not null;

5.查询会话之间锁等待的关系

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.查询锁等待事件

select * from v$session_wait where event=‘enqueue‘;

解决方案：

select session_id from v$locked_object; --首先得到被锁对象的session_id

SELECT sid, serial#, username, osuser FROM v$session where sid = session_id; --通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。

ALTER SYSTEM KILL SESSION ‘sid,serial‘;

example:

ALTER SYSTEM KILL SESSION ‘13, 8‘;

原文：http://www.cnblogs.com/ys-wuhan/p/5956983.html