ORA-00054:资源正忙,要求指定NOWAIT解决

问题描述

业务人员在insert时发生报错

原理分析

1.创建索引时会产生的锁
2.dml 语句会产生的锁
3.索引创建时加上关键字 online时产生的锁

解决步骤

1.等待其他会话释放资源
2.找出占用资源的会话，并删除
3.重启数据库

解决：

SQL> select session_id from v$locked_object;

SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = xx;

SQL> ALTER SYSTEM KILL SESSION 'xx,xxx';

查看被锁的表和引起锁表的session：

SQL> select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

SQL> 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;

SQL> select xidusn,object_id,session_id, oracle_username,os_user_name from v$locked_object ;

SQL> select object_id,object_name from dba_objects where object_id='145943';         #  第三步查到的object id

SQL> alter system kill session '25,1' ;     # 第一步查到的session id和第二步查到的serial#

ORA-00054解决参考