查出引起等待的sql，並kill該引起等待的sql

/*
*時間：2009-04-27  Monday
*環境：AIX5.3   Oracle10g10.2.0.1.0
*標題：查出引起等待的sql，並kill該引起等待的sql
*/
這是由資料庫的觸發器沒有及時提交導致簡單的sql進行漫長的等待。

執行簡單的sql語句：
update comm_coding_sort_detail t
set t.coding_name='6廠水平井'
where t.coding_code_id='0300100011000001278'

但一直處於等待狀態。

--1查詢出哪些session在等待。
select sid,username,blocking_session,blocking_session_status,blocking_instance from v$session where username='DQOMSWAS';

        SID USERNAME BLOCKING_SESSION BLOCKING_SESSION_STATUS BLOCKING_INSTANCE
1 454 DQOMSWAS                  NO HOLDER 
2 455 DQOMSWAS                  NO HOLDER 
3 456 DQOMSWAS                  NO HOLDER 
4 457 DQOMSWAS                  NOT IN WAIT 
5 464 DQOMSWAS                  NO HOLDER 
6 465 DQOMSWAS                  NO HOLDER 
7 472 DQOMSWAS 489                 VALID 1
8 473 DQOMSWAS                  NO HOLDER 
9 474 DQOMSWAS                  NO HOLDER 
 
可以檢視出Blocking_session為489 的sql導致了session為472的等待。

--2
select * from dba_waiters;

  WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
 513          472             Transaction    Exclusive Exclusive 458791         28659

session 513等待導致了472的等待

--3
select * from dba_blockers;

HOLDING_SESSION
472

--4
select sid,username,blocking_session,event,wait_time
from v$session where username='DQDT';

    SID USERNAME BLOCKING_SESSION EVENT WAIT_TIME
1 461 DQDT  SQL*Net message from client 0
2 483 DQDT  SQL*Net message from client 0
3 485 DQDT  SQL*Net message from client 0
4 488 DQDT  SQL*Net message from client 0
5 498 DQDT  SQL*Net message from client 0
6 513 DQDT 472 enq: TX - row lock contention 0
7 514 DQDT  SQL*Net message from client 0

可以看出使用者DQDT使用者的sid 513 一直在等待，導致sid 472不能執行

--5根據sid查出具體的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

  sid 472為：
  update comm_coding_sort_detail t
set t.coding_name='6廠水平井'
where t.coding_code_id='0300100011000001278'

 sid 513為：
update dqomswas.COMM_CODING_SORT_DETAIL set END_IF=:1,CREATOR_ID
=:2,SPARE2=:3,CITE_IF=:4,LOCKED_IF=:5,CODING_SHOW_ID=:6,CREATOR=
:7,CREATE_DATE=:8,SPARE1=:9,CODE_AFFORD_ORG_ID=:10,MODIFI_DATE=:
11,CODING_SORT_ID=:12,CODING_MNEMONIC_ID=:13,CODING_CODE=:14,COD
ING_NAME=:15,NOTE=:16,SUPERIOR_CODE_ID=:17,BSFLAG=:18,EDITION_NA
MEPLATE=:19,CODING_CODE_ID=:20 where CODING_CODE_ID='03001000110
00001278'
這個sid 513語句是由於資料庫中的資料傳輸時執行了一個觸發器，但一直沒有提交。

--6 查詢等待中的sid,serial#
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_time
1 DQDT 513 5 2009-4-15 6:57:41
2 DQDT 513 5 2009-4-15 6:57:41
3 DQOMSREAD 489 5406 2009-4-27 15:43:11
4 DQOMSWAS 472 39620 2009-4-27 16:06:11

（確定如果刪除sid 513的sql語句，不會影響資料庫的資料，則可以執行下面的kill操作。）
--7 kill sid 513
alter system kill session '513,5';

剛才的更新sql就可以執行了。