個(gè)人中心
退出
查看和終止 Oracle 連接會(huì)話(huà)以及正在執(zhí)行的 SQL 語(yǔ)句
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
查看當(dāng)前連接的會(huì)話(huà)
終止正在執(zhí)行的 SQL 語(yǔ)句
終止指定的連接會(huì)話(huà)
通過(guò) SQL 命令終止連接會(huì)話(huà)
通過(guò)操作系統(tǒng)命令終止連接會(huì)話(huà)
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。作為 DBA，有時(shí)候?yàn)榱藞?zhí)行管理操作需要終止當(dāng)前的用戶(hù)會(huì)話(huà)；當(dāng)某個(gè) SQL
語(yǔ)句長(zhǎng)時(shí)間運(yùn)行并占用大量系統(tǒng)資源時(shí)，我們也可能需要手動(dòng)終止正在執(zhí)行的語(yǔ)句。因此，今天我們來(lái)討論一下如何查看和終止 Oracle
的連接會(huì)話(huà)，以及正在執(zhí)行的 SQL 語(yǔ)句。
查看當(dāng)前連接的會(huì)話(huà)
Oracle 系統(tǒng)視圖 V$SESSION 和對(duì)應(yīng)的 GV$SESSION（全局視圖主要用于 RAC 環(huán)境）提供了當(dāng)前所有會(huì)話(huà)的相關(guān)信息。例如：
select inst_id, sid, serial#, username,
status, server, program, sql_id
from gv$session
where type != 'BACKGROUND'
and username is not null
and nvl(osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|USERNAME|STATUS |SERVER |PROGRAM |SQL_ID |
-------|---|-------|--------|--------|---------|----------------------------------------|-------------|
1|143| 9323|TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|9b1bawv3upw70|
1|265| 24368|TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |3fnstj6ydy4xk|
1|399| 16226|TONY |INACTIVE|DEDICATED|sqlplus@sqlhost (TNS V1-V3) | |
以上查詢(xún)只返回了用戶(hù)的連接會(huì)話(huà)，不包括后臺(tái)進(jìn)程的連接會(huì)話(huà)。其中，
inst_id 是會(huì)話(huà)所在的實(shí)例 id；
sid 是會(huì)話(huà)標(biāo)識(shí)符，serial# 是會(huì)話(huà)序列號(hào)，兩者唯一確定實(shí)例中的會(huì)話(huà)；
username 是連接數(shù)據(jù)庫(kù)的用戶(hù)；
status 是會(huì)話(huà)的狀態(tài)，例如 ACTIVE、INACTIVE、KILLED 等；
server 是服務(wù)器的類(lèi)型，例如 DEDICATED、SHARED 等；
program 是會(huì)話(huà)客戶(hù)端的應(yīng)用程序名；
sql_id 是正在執(zhí)行的 SQL 語(yǔ)句 id。
??關(guān)于 V$SESSION 視圖的更多字段可以參考官方文檔。
終止正在執(zhí)行的 SQL 語(yǔ)句
從 Oracle 18c 開(kāi)始增加了ALTER SYSTEM CANCEL SQL命令，可以用于終止指定會(huì)話(huà)中正在執(zhí)行的 SQL 語(yǔ)句，但不會(huì)終止連接會(huì)話(huà)。該語(yǔ)句的語(yǔ)法如下：
ALTER SYSTEM CANCEL SQL 'sid, serial# [, @inst_id][, sql_id]';
其中，
sid 是會(huì)話(huà)標(biāo)識(shí)符；
serial# 是會(huì)話(huà)序列號(hào)；
inst_id 是實(shí)例 id，如果省略表示當(dāng)前實(shí)例；
sql_id 是 SQL 標(biāo)識(shí)符，如果省略表示終止該會(huì)話(huà)中正在運(yùn)行的語(yǔ)句。
以上輸入信息都可以通過(guò)查詢(xún)上文中的 V$SESSION 或者 GV$SESSION 視圖獲得。使用該語(yǔ)句可以終止高負(fù)載的 SQL 語(yǔ)句；如果終止的是 DML 語(yǔ)句，該語(yǔ)句將會(huì)被回滾。
我們首先在一個(gè)會(huì)話(huà)（sid = 399、serial# = 16226）中執(zhí)行以下語(yǔ)句：
SQL> EXEC DBMS_LOCK.sleep(300);
該會(huì)話(huà)將會(huì)睡眠 300 秒，然后在另一個(gè)會(huì)話(huà)中終止該語(yǔ)句（可以再次查詢(xún) GV$SESSION 視圖獲得相關(guān)信息）：
-- 另一個(gè)會(huì)話(huà)
ALTER SYSTEM CANCEL SQL '399, 16226';
此時(shí)，上面的第一個(gè)會(huì)話(huà)返回以下錯(cuò)誤：
SQL> EXEC DBMS_LOCK.sleep(300);
BEGIN DBMS_LOCK.sleep(300); END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 205
ORA-06512: at line 1
??如果想要查看會(huì)話(huà)中正在執(zhí)行的 SQL 語(yǔ)句內(nèi)容，可以通過(guò) sql_id 字段關(guān)聯(lián)查詢(xún)系統(tǒng)視圖 V$SQL 或者 GV$SQL。
終止指定的連接會(huì)話(huà)
通過(guò) SQL 命令終止連接會(huì)話(huà)
??如果終止了錯(cuò)誤的會(huì)話(huà)，可能會(huì)造成嚴(yán)重的影響，因此在確定要終止的會(huì)話(huà)時(shí)要格外小心。如果終止了后臺(tái)進(jìn)程對(duì)應(yīng)的會(huì)話(huà)，將會(huì)導(dǎo)致實(shí)例崩潰。
當(dāng)會(huì)話(huà)終止被時(shí)，該會(huì)話(huà)的所有活動(dòng)事務(wù)都會(huì)被回滾，并且該會(huì)話(huà)持有的資源（例如鎖和內(nèi)存）將立即釋放。終止用戶(hù)會(huì)話(huà)的基于命令如下：
ALTER SYSTEM KILL SESSION 'sid, serial# [, @inst_id]';
其中，
sid 是會(huì)話(huà)標(biāo)識(shí)符；
serial# 是會(huì)話(huà)序列號(hào)；
inst_id 是實(shí)例 id，如果省略表示當(dāng)前實(shí)例。
以上輸入信息都可以通過(guò)查詢(xún)上文中的 V$SESSION 或者 GV$SESSION 視圖獲得。
我們使用以下語(yǔ)句終止指定會(huì)話(huà)（sid = 399、serial# = 16226）：
alter system kill session '399, 16226';
此時(shí)再次查詢(xún) gv$session 可以看到該會(huì)話(huà)的狀態(tài)為 KILLED：
select inst_id, sid, serial#, username,
status, server, program, sql_id
from gv$session s
where type != 'BACKGROUND'
and username is not null
and nvl(osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|USERNAME|STATUS |SERVER |PROGRAM |SQL_ID |
-------|---|-------|--------|--------|---------|----------------------------------------|-------------|
1|143| 9323|TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|b3upx6u952qfb|
1|265| 24368|TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |3fnstj6ydy4xk|
1|399| 16226|TONY |KILLED |DEDICATED|sqlplus@sqlhost (TNS V1-V3) | |
如果我們?cè)诒唤K止的會(huì)話(huà)中執(zhí)行操作，將會(huì)返回以下錯(cuò)誤：
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 27994
Session ID: 399 Serial number: 16226
ALTER SYSTEM KILL SESSION 語(yǔ)句實(shí)際上并不會(huì)立即終止會(huì)話(huà)，它只是請(qǐng)求會(huì)話(huà)自己終止。在某些情況下，例如等待遠(yuǎn)程數(shù)據(jù)庫(kù)的響應(yīng)或者事務(wù)的回滾時(shí)，會(huì)話(huà)不會(huì)立即終止，而是等待當(dāng)前操作完成。此時(shí)會(huì)話(huà)的狀態(tài)為 KILLED，表示“標(biāo)記為終止”。
ALTER SYSTEM KILL SESSION 命令支持使用 IMMEDIATE 選項(xiàng)：
ALTER SYSTEM KILL SESSION 'sid, serial# [, @inst_id]' IMMEDIATE;
IMMEDIATE 選項(xiàng)不會(huì)改變?cè)撁顖?zhí)行的操作，但是會(huì)立即返回，而不會(huì)等待會(huì)話(huà)被終止。
除了以上命令之外，ALTER SYSTEM DISCONNECT SESSION 語(yǔ)句也可以用于終止會(huì)話(huà)：
DISCONNECT SESSION 'sid, serial#' POST_TRANSACTION；
DISCONNECT SESSION 'sid, serial#' IMMEDIATE；
該命令與 ALTER SYSTEM KILL SESSION 語(yǔ)句不同之處在于它會(huì)終止專(zhuān)用服務(wù)器進(jìn)程（或者共享服務(wù)器進(jìn)程中的虛擬鏈路，相當(dāng)于通過(guò)操作系命令終止服務(wù)器進(jìn)程。其中 POST_TRANSACTION 選項(xiàng)表示等待正在執(zhí)行中的事務(wù)結(jié)束后終止連接，IMMEDIATE 選項(xiàng)表示立即斷開(kāi)連接并且回滾事務(wù)。
對(duì)于上面終止會(huì)話(huà)的示例（如果沒(méi)有執(zhí)行的話(huà)），也可以使用以下語(yǔ)句實(shí)現(xiàn)相同的目的：
alter system disconnect session '399, 16226' post_transaction;
這種方式可以避免通過(guò)操作系統(tǒng)命令終止會(huì)話(huà)，減少了錯(cuò)誤操作的風(fēng)險(xiǎn)。
通過(guò)操作系統(tǒng)命令終止連接會(huì)話(huà)
如果想要通過(guò)操作系統(tǒng)命令終止會(huì)話(huà)，首先需要找出會(huì)話(huà)對(duì)應(yīng)的操作系統(tǒng)進(jìn)程 id，可以通過(guò) V$PROCESS 或者 GV$PROCESS 進(jìn)行查看：
select s.inst_id,
s.sid,
s.serial#,
s.sql_id,
p.spid,
s.username,
s.status,
s.server,
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'
and s.username is not null
and nvl(s.osuser,'x') <> 'SYSTEM';
INST_ID|SID|SERIAL#|SQL_ID |SPID |USERNAME|STATUS |SERVER |PROGRAM |
-------|---|-------|-------------|-----|--------|--------|---------|----------------------------------------|
1|399| 22691| |12195|TONY |INACTIVE|DEDICATED|sqlplus@sqlhost (TNS V1-V3) |
1|265| 24368|3fnstj6ydy4xk|9152 |TONY |INACTIVE|DEDICATED|DBeaver 7.1.3 - Main |
1|143| 9323|7yyukqn3pkt89|9193 |TONY |ACTIVE |DEDICATED|DBeaver 7.1.3 - SQLEditor <Script-2.sql>|
其中，spid 就是會(huì)話(huà)對(duì)應(yīng)的操作系統(tǒng)進(jìn)程 id。
有了進(jìn)程 id ，就可以通過(guò)操作系統(tǒng)命令終止相應(yīng)的進(jìn)程。對(duì)于 Unix/Linux 操作系統(tǒng)，可以使用 kill 命令終止進(jìn)程：
kill spid
kill -9 spid
其中，-9 表示強(qiáng)制終止。在終止進(jìn)程之前也可以使用 ps 命令進(jìn)行二次確認(rèn)：
ps -ef | grep ora
我們使用以下命令終止會(huì)話(huà)（sid = 399、serial# = 22691）對(duì)應(yīng)的操作系統(tǒng)進(jìn)程（12195）：
[root@sqlhost ~]# kill 12195
此時(shí)，如果在該會(huì)話(huà)中執(zhí)行任何語(yǔ)句都會(huì)返回錯(cuò)誤：
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 12195
Session ID: 399 Serial number: 22691
??Oracle 12c 開(kāi)始支持 Unix/Linux 環(huán)境下的多線(xiàn)程模型；如果使用了這種模型，不要使用操作系統(tǒng)命令終止會(huì)話(huà)，否則會(huì)一次終止多個(gè)會(huì)話(huà)。
對(duì)于 Windows 操作系統(tǒng)，可以使用 orakill 命令行工具終止進(jìn)程：
orakill sid spid
其中，sid 是會(huì)話(huà)標(biāo)識(shí)符，spid 是會(huì)話(huà)對(duì)應(yīng)的進(jìn)程 id。
總結(jié)
Oracle 提供了幾種終止連接會(huì)話(huà)/進(jìn)程和運(yùn)行中的 SQL 語(yǔ)句的方法。首先可以通過(guò) GV$SESSION 和 GV$PROCESS 等視圖查看會(huì)話(huà)和對(duì)應(yīng)的系統(tǒng)進(jìn)程信息，然后使用 ALTER SYSTEM KILL SESSION 或者 ALTER SYSTEM DISCONNECT SESSION 命令終止連接會(huì)話(huà)，或者利用操作系統(tǒng)命令（kill、orakill）終止相應(yīng)的進(jìn)程；另外，也可以使用 ALTER SYSTEM CANCEL SQL 命令終止正在運(yùn)行的 SQL 語(yǔ)句。
如果覺(jué)得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！
分類(lèi)導(dǎo)航