在進行資料庫維護的過程中要删除一個中間表,遇到如下錯誤:
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被鎖住,下面給出錯誤的處理思路和過程,具體情況而異:
<b>1 檢視被資料庫中被鎖的使用者資訊:</b>
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的資訊,
<b>2 查詢出sql資訊根據實際情況,進行操作</b>
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.
<b>正是發起的那個語句,檢視使用者的資訊進行确認</b>
sys@dw>@user_info
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.
<b>3 選擇kill 掉程序 </b>
這裡知道此session 可以殺掉,是以殺掉此session
sys@dw>alter system kill session '1594,7385';
system altered.
<b>4 進行确認: </b>
在資料庫确認
sys@dw>@user_info
no rows selected
username sid serial# logon_time
--------------- ------- ------- -------------------
dwods 1520 42477 2011-11-17 18:00:40
2 rows selected.
<b>在os層确認,程序已經被殺</b>。
oracle@dw1:/home/oracle>ps -ef | grep 3309
oracle 22565 18543 0 18:59 pts/5 00:00:00 grep 3309
<b>再次執行删除表的操作:</b>
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
/