ORACLE快速徹底Kill掉的會話,防止鎖表

2020-05-30

編程之家收集整理的這篇文章主要介紹了ORACLE快速徹底Kill掉的會話,防止鎖表，編程之家小編覺得挺不錯的，現在分享給大家，也給大家做個參考。

萬能是sql重啟服務，不然，以下之：

在ORACLE數據庫當中，有時候會使用ALTER SYSTEM KILL SESSION 'sid,serial#'殺掉一個會話進程，但是使用這個sql語句殺掉會話后，數據庫并不會立即釋放掉相關的資源，有時候你會發現鎖定的資源很長時間也不會釋放，即使會話狀態為“KILLED”，依然會阻塞其它會話。

下面根據Eygel的"Oracle中Kill session的研究"，構造一個案例看看kill session到底做了什么。如下所示

會話1：

Connected.

sql> update test set status='invalid';

55944 rows updated.

sql> update test2 set dropped='Y';

3090 rows updated.

會話2：

user

USER is "SYS"

update etl.test2 3090 會話3

select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username ='sys');

SADDR SID SERIAL# PADDR USERNAME STATUS

---------------- ---------- ---------- ---------------- ------------------------------ --------

000000025C233B00 27 33353 000000025F1D1FC8 ETL INACTIVE

000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE

000000025C24BC50 63 54311 000000025F1D5F08 SYS ACTIVE

alter system kill '27,33353';

System altered.

SADDR SID SERIAL# PADDR USERNAME STATUS

--------------000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED

000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE

000000025C24BC50 63 54311 000000025F1D5F08 SYS INACTIVE

如下所示，我殺掉了其中兩個會話后，這兩個會話的地址都變為000000025C21A0B0了(請見PADDR列)。當在Oracle中kill session以后,Oracle只是簡單的把相關session的paddr 指向同一個虛擬地址.此時v$process和v$session失去關聯,進程就此中斷。然后Oracle就等待PMON去清除這些Session.所以通常等待一個被標記為Killed的Session退出需要花費很長的時間.如果此時被Kill的process，重新嘗試執行任務，那么馬上會收到進程中斷的提示，process退出，此時Oracle會立即啟動PMON來清除該session.這被作為一次異常中斷處理.

'63,54311';

000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED

000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED

我們根據下面sql找到進程的地址，然后在v$process里面找到對應的spid,然后從操作系統中殺掉該進程。

select p.addr from v$process p where pid <> 1

2 minus

3 select s.paddr session s;

ADDR

--

000000025F1D1FC8

000000025F1D5F08

session

000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED

select addr,pid,spid,username from v$process where addr in ('000000025F1D1FC8','000000025F1D5F08');

ADDR PID SPID USERNAME

-- ---------- ------------ ---------------

000000025F1D1FC8 22 12959 oracle

000000025F1D5F08 38 12971 oracle

sql> ! kill -9 12959

kill -9 12971

000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE

在ORACLE數據庫殺掉會話進程有三種方式：

1： ALTER SYSTEM KILL SESSION

關于KILL SESSION Clause ，如下官方文檔描述所示，alter system kill session實際上不是真正的殺死會話，它只是將會話標記為終止。等待PMON進程來清除會話。

The KILL SESSION clause lets you mark a session as terminated,roll back ongoing transactions,release all session locks,and partially recover session resources. To use this clause,your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session with the following values from the V$SESSION view:

For integer1,specify the value of the SID column.

For integer2,specify the value of the SERIAL# column.

For the optional integer3,specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.

If the session is performing some activity that must be completed,such as waiting for a reply from a remote database or rolling back a transaction,255)">then Oracle Database waits for this activity to complete,marks the session as terminated,and then returns control to you. If the waiting lasts a minute,255)">then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

Whether or not the session has an ongoing transaction,Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.

可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE 來快速回滾事物、釋放會話的相關鎖、立即返回當前會話的控制權。

Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions,recover the entire session state,and return control to you immediately.

2： ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM DISCONNECT SESSION 殺掉專用服務器(DEDICATED SERVER)或共享服務器的連接會話，它等價于從操作系統殺掉進程。它有兩個選項POST_TRANSACTION和IMMEDIATE， 其中POST_TRANSACTION表示等待事務完成后斷開會話，IMMEDIATE表示中斷會話，立即回滾事務。

sql> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

3： KILL -9 SPID (Linux) 或 orakill ORACLE_SID spid　(Windows)

可以使用下面sql語句找到對應的操作系統進程SPID，然后殺掉。當然殺掉操作系統進程是一件危險的事情，尤其不要誤殺。所以在執行前，一定要謹慎確認。

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,244)"> s.serial#,monospace; width:878.046875px; padding:0px; direction:ltr; line-height:12pt; background-color:white"> p.spid,244)"> s.username,monospace; width:878.046875px; padding:0px; direction:ltr; line-height:12pt; background-color:white"> s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

總結

以上是生活随笔為你收集整理的oracle锁表会话超时时间,ORACLE快速彻底Kill掉的会话,防止锁表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。