oracle 查詢殺死死鎖方案

 

一、資料庫死鎖的現象       首先確認登入的介面  sys/dba
程式在執行的過程中，點選確定或儲存按鈕，程式沒有響應，也沒有出現報錯。 
 

二、死鎖的原理

當對於資料庫某個表的某一列做更新或刪除等操作，執行完畢後該條語句不提 

交，另一條對於這一列資料做更新操作的語句在執行的時候就會處於等待狀態， 

此時的現象是這條語句一直在執行，但一直沒有執行成功，也沒有報錯。 

--------------------------------------&gt>涉及到的資料字典:

-------&gt>系統級別

----------&gt>會話級別:

---------&gt>官方文件相關指南:

10.1.3.3 Dynamic Performance Views Containing Wait Event Statistics

These dynamic performance views can be queried for wait event statistics:

• V$ACTIVE_SESSION_HISTORY

  The V$ACTIVE_SESSION_HISTORY view displays active database session activity, sampled once every second. See "Active Session History".

• V$SESS_TIME_MODEL and V$SYS_TIME_MODEL

  The V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views contain time model statistics, including DB time which is the total time spent in database calls.

• V$SESSION_WAIT

  The V$SESSION_WAIT view displays information about the current or last wait for each session (such as wait ID, class, and time).

• V$SESSION

  The V$SESSION view displays information about each current session and contains the same wait statistics as those found in the V$SESSION_WAIT view. If applicable, this view also contains detailed information about the object that the session is currently waiting for (such as object number, block number, file number, and row number), the blocking session responsible for the current wait (such as the blocking session ID, status, and type), and the amount of time waited.

• V$SESSION_EVENT

  The V$SESSION_EVENT view provides summary of all the events the session has waited for since it started.

• V$SESSION_WAIT_CLASS

  The V$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for each session.

• V$SESSION_WAIT_HISTORY

  The V$SESSION_WAIT_HISTORY view displays information about the last ten wait events for each active session (such as event type and wait time).

• V$SYSTEM_EVENT

  The V$SYSTEM_EVENT view provides a summary of all the event waits on the instance since it started.

• V$EVENT_HISTOGRAM

  The V$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis.

• V$FILE_HISTOGRAM

  The V$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.

• V$SYSTEM_WAIT_CLASS

  The V$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent in each class of wait events.

• V$TEMP_HISTOGRAM

  The V$TEMP_HISTOGRAM view displays a histogram of times waited during single block reads for each temporary file.

--------------------------------------------------------------&gt>

三、死鎖的定位方法

通過檢查資料庫表，能夠檢查出是哪一條語句被死鎖，產生死鎖的機器是哪一臺。 

 

1）用dba使用者執行以下語句 

select username,lockwait,status,machine,program from v$session where sid in 

(select session_id from v$locked_object) 

如果有輸出的結果，則說明有死鎖，且能看到死鎖的機器是哪一臺。欄位說明： 

Username：死鎖語句所用的資料庫使用者； 

Lockwait：死鎖的狀態，如果有內容表示被死鎖。 

Status： 狀態，active表示被死鎖 

Machine： 死鎖語句所在的機器。 

Program： 產生死鎖的語句主要來自哪個應用程式。 

 

2）用dba使用者執行以下語句，可以檢視到被死鎖的語句。 

select sql_text from v$sql where hash_value in  

(select sql_hash_value from v$session where sid in 

(select session_id from v$locked_object))
 

四、死鎖的解決方法

     一般情況下，只要將產生死鎖的語句提交就可以了，但是在實際的執行過程中。使用者可 

能不知道產生死鎖的語句是哪一句。可以將程式關閉並重新啟動就可以了。 

　經常在Oracle的使用過程中碰到這個問題，所以也總結了一點解決方法。

 

1）查詢死鎖的程式：

sqlplus "/as sysdba" (sys/change_on_install)

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,

l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 

FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

 

2）kill掉這個死鎖的程式：

　　alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

 

3）如果還不能解決：

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

 

　　其中sid用死鎖的sid替換: exit

ps -ef|grep spid

 

　　其中spid是這個程式的程式號，kill掉這個Oracle程式

 

      KILL -9  “剛才查出的SPID”

      在WINDOWS平臺，可以是偶那個orakill。

4）查詢死鎖語句

select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID, 

 

       B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,

       ''''||C.Session_ID||','||B.SERIAL#||''''

from v$sql A, v$session B, v$locked_object C

where A.HASH_VALUE = B.SQL_HASH_VALUE and

B.SID = C.Session_ID

 

精簡處理步驟

--1、查詢被鎖表的object_id

select object_id from all_objects where object_name = upper('table_name') and object_type = 'TABLE'

 

--2、根據第1步查到的object_id查詢被鎖物件的會話ID

select session_id from v$locked_object where object_id = 1779474

 

--3、根據第2步查到的session_id查詢serial#

select sid,serial# from v$session  where sid = 284

 

--4、根據session_id和serial#殺掉程式

alter system kill session 'sid,serial#'

 

--查詢在被鎖物件上的操作語句

select sql_text from v$sqlarea where address = '00'  --address為v$session中的sql_address欄位