【Oracle】ORA-00054 错误解决方法
在进行数据库维护的过程中要删除一个中间表，遇到如下错误：
sys@DW>drop table dwods.member_DELTA;
drop table dwods.member_DELTA
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
发现表因为执行dml被锁住，下面给出错误的处理思路和过程，具体情况而异：
1 查看被数据库中被锁的用户信息：
sys@DW>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
------------------------------ ---------- ---------- -------------------
DWODS 1520 42477 2011-11-17 18:00:40
DWODS 1594 7385 2011-11-17 18:41:27
dwods 被锁住，因为事务是18：41分发起的，所以查看一下sid 为1594的信息，
2 查询出sql信息根据实际情况，进行操作
sys@DW>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: 1594
old 3: and a.sid=&sid order by piece
new 3: and a.sid=1594 order by piece
SQL_TEXT
----------------------------------------------------------------
insert /*+ append +*/ into DWODS.MEMBER_delta (ACTION,
ADDRESS,
........
32 rows selected.
正是发起的那个语句，查看用户的信息进行确认
sys@DW>@user_info
Enter value for sid: 1594
old 12: where a.sid = &sid
new 12: where a.sid = 1594
USERNAME SID SERIAL# OS Process Logon time OSUSER PROGRAM STATUS
--------- ----- ------- ----------------------------- -------- ----------------------- -----------
DWODS 1594 7385 3309 17/11/2011 18:41:27 etl sqlplus@dw1 (TNS V1-V3) ACTIVE
1 row selected.
3 选择kill 掉进程
这里知道此session 可以杀掉，所以杀掉此session
sys@DW>alter system kill session '1594,7385';
System altered.
4 进行确认：
在数据库确认
sys@DW>@user_info
Enter value for sid: 1594
old 12: where a.sid = &sid
new 12: where a.sid = 1594
no rows selected
sys@DW>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
--------------- ------- ------- -------------------
DWODS 1520 42477 2011-11-17 18:00:40
DWODS 1520 42477 2011-11-17 18:00:40
2 rows selected.
在os层确认，进程已经被杀。
oracle@dw1:/home/oracle>ps -ef | grep 3309
oracle 22565 18543 0 18:59 pts/5 00:00:00 grep 3309
再次执行删除表的操作：
sys@DW>drop table dwods.member_DELTA;
Table dropped.
------附上：
user_info。sql 脚本的内容：
select
a.username,
a.sid,
a.serial#,
b.spid "OS Process",
to_char(a.logon_time,'DD/MM/YYYY hh24:mi:ss') "Logon time",
a.osuser,
a.program,
a.status
from v$session a, v$process b
where a.sid = &sid
and a.paddr = b.addr
/
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
