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

最新推荐文章于 2025-06-13 16:19:04 发布

原创最新推荐文章于 2025-06-13 16:19:04 发布 · 937 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #ora #00054 #deadlock #blocking

Oracle oerr 专栏收录该内容

21 篇文章

订阅专栏

本文记录了在尝试删除Oracle数据库中的表时遇到资源忙错误的解决过程。通过查询锁定对象和会话信息定位到阻塞的事务，并进一步查询到具体的阻塞SQL，最终通过系统管理员权限杀死阻塞会话成功释放资源并完成表的删除。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

EODA@PROD1> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


EODA@PROD1> select t2.username,t2.sid,t2.serial#,t2.logon_time   --查询阻塞事务
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid order by t2.logon_time; 

USERNAME			      SID    SERIAL# LOGON_TIM
------------------------------ ---------- ---------- ---------
EODA				       50	 771 18-OCT-16

EODA@PROD1> select sql_text from v$session a,v$sqltext_with_newlines b  --查询阻塞的语句
  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
  and a.sid=&sid order by piece;  
Enter value for sid: 50
old   3:   and a.sid=&sid order by piece
new   3:   and a.sid=50 order by piece

SQL_TEXT
----------------------------------------------------------------
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

EODA@PROD1> alter system kill session '50,771';  --kill事务

System altered.

EODA@PROD1> drop table t purge;

Table dropped.