六、ORA-00054错误

king's blog
在oracle的日常使用中,经常碰到千奇百怪的问题,从本节开始,我们来看看常见的问题及处理方法。有个小技巧,oracle的操作错误,oracle都会给出一个错误码,比如ORA-00054、ORA-00600,ORA-00607等等,本节我们先来看看ORA-00054错误的处理办法。

问题描述:drop table时报ORA-00054错误

比如:通过SQL语句 drop table SD_WRD_HOL_LST 报如下错误

1
2
3
4
5
6
SQL> @drop_reaps_tables_tmp.sql
SQL> drop table SD_WRD_HOL_LST cascade constraint;
drop table SD_WRD_HOL_LST cascade constraint
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

原因分析:

当某个数据库用户在数据库中插入、更新、删除一个表的数据,或者增加一个表的主键时或者表的索引时,常出现ora-00054:resource busy and acquire withnowait specified的错误。主要是因为有事务正在执行(或者事务已经被锁),所有导致执行不成功

解决方案:

步骤一:用DBA权限的用户查看数据库都有哪些被锁

1
2
3
4
5
6
7
8
9
10
oracle@ETS157:~/backup/restore> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 27 19:26:49 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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;
USERNAME SID SERIAL# LOGON_TIM
------------------------------ ---------- ---------- ---------
REAPS_DEV 50 4395 27-NOV-14

表示被锁的用户是REAPS_DEV,SID=50,Serial=4395

步骤二:根据SID查到具体的事务ID,然后在数据库中删除

1
2
SQL > 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;
SQL> DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1 (1代表上面sql查询出来的结果)

步骤三:根据步骤一的查询结果,kill掉该事务

1
SQL > alter system kill session '50,4395';

步骤四:重新删除表

1
SQL > drop table SD_WRD_HOL_LST;
Tags:
文 / 甲乙丙丁
LEAVE A REPLY

loading