当某个数据库用户在数据库中插入、更新、删除一个表的数据,或者增加一个表的主键时或者表的索引时,常常会出现这样的错误。
ora-00054:resource busy and acquire with nowait specified
主要是因为有事务正在执行(或者事务已经被锁),所有导致执行不成功。
这个时候需要使用sys用户
sys@TEST>select t2.username,t2.sid,t2.serial#,t2.logon_time 2 from v$locked_object t1,v$session t2 3 where t1.session_id=t2.sid order by t2.logon_time; USERNAME SID SERIAL# LOGON_TIME ------------------------------------------------------------ ---------- ---------- ------------ SCOTT 26 1715 25-APR-17
根据sid查看具体的sql语句,如果sql不重要,可以kill
sys@TEST>select sql_text from v$session a,v$sqltext_with_newlines b 2 where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value 3 and a.sid=&sid order by piece; Enter value for sid: 26 old 3: and a.sid=&sid order by piece new 3: and a.sid=26 order by piece SQL_TEXT ---------------------------------------------------------------------------------------------------- BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
kill该事务
sys@TEST>alter system kill session '26,1715'; System altered.
然后就可以继续使用了