當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

Oracle 彻底 kill session

發(fā)布時(shí)間：2023/11/29 编程问答 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle 彻底 kill session 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

為什么80%的碼農(nóng)都做不了架構(gòu)師？>>> ??

--***************************

-- Oracle 徹底 kill session

--***************************

? kill session 是DBA經(jīng)常碰到的事情之一。如果kill 掉了不該kill 的session，則具有破壞性，因此盡可能的避免這樣的錯(cuò)誤發(fā)生。同時(shí)也應(yīng)當(dāng)注意，

如果kill 的session屬于Oracle 后臺(tái)進(jìn)程，則容易導(dǎo)致數(shù)據(jù)庫(kù)實(shí)例宕機(jī)。

? 通常情況下，并不需要從操作系統(tǒng)級(jí)別殺掉Oracle會(huì)話(huà)進(jìn)程，但并非總是如此，下面的描述中給出了在Oracle級(jí)別殺掉會(huì)話(huà)以及操作系統(tǒng)級(jí)別殺掉進(jìn)程。

一、獲得需要kill session的信息(使用V$SESSION 和 GV$SESSION視圖)

? SET LINESIZE 180

? COLUMN spid FORMAT A10

? COLUMN username FORMAT A10

? COLUMN program FORMAT A40

? SELECT s.inst_id,

???????? s.sid,

???????? s.serial#,

????? ???p.spid,

???????? s.username,

???????? s.program,

???????? s.paddr,

???????? s.STATUS

? FROM?? gv$session s

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

? WHERE? s.type != 'BACKGROUND';

???? INST_ID??????? SID??? SERIAL# SPID?????? USERNAME?? PROGRAM?????????????????????????????????????? PADDR??? STATUS

? ---------- ---------- ---------- ---------- ---------- --------------------------------------------- -------- --------

?????????? 1??????? 146???????? 23 27573????? TEST?? ????sqlplus@oracle10g (TNS V1-V3)???????????????? 4C621950 INACTIVE

?????????? 1??????? 160???????? 17 27610????? SYS??????? sqlplus@oracle10g (TNS V1-V3)???????????????? 4C624174 ACTIVE

?????????? 1??????? 144???????? 42 27641????? SCOTT????? sqlplus@oracle10g (TNS V1-V3)???????????????? 4C624730 INACTIVE

????????

二、使用ALTER SYSTEM KILL SESSION 命令實(shí)現(xiàn)

? 語(yǔ)法：

????? SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

????? SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

????

??? 對(duì)于RAC環(huán)境下的kill session ,需要搞清楚需要kill 的session 位于哪個(gè)節(jié)點(diǎn)，可以查詢(xún)GV$SESSION視圖獲得。

??? kill session 的時(shí)候僅僅是將會(huì)話(huà)殺掉。在有些時(shí)候，由于較大的事務(wù)或需要運(yùn)行較長(zhǎng)的SQL語(yǔ)句將導(dǎo)致需要kill的session并不能立即殺掉。對(duì)于這種情

??? 況將收到 "marked for kill"提示(如下)，一旦會(huì)話(huà)當(dāng)前事務(wù)或操作完成，該會(huì)話(huà)被立即殺掉。

???

?? ?alter system kill session '4730,39171'

??? *

??? ERROR at line 1:

??? ORA-00031: session marked for kill

? 在下面的操作中將殺掉會(huì)話(huà)146，144

??? sys@AUSTIN> alter system kill session '146,23';

???

??? System altered.

???

??? sys@AUSTIN> alter system kill session '144,42';

???

??? System altered.

???

??? sys@AUSTIN> select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session where username is not null;

???

?????? INST_ID SADDR?????????? SID??? SERIAL# PADDR??? USERNAME?? STATUS?? PROGRAM

??? ---------- -------- ---------- ---------- -------- ---------- -------- ---------------------------------------------

?????????? ??1 4C70BF04??????? 144???????? 42 4C6545A0 SCOTT????? KILLED?? sqlplus@oracle10g (TNS V1-V3)

???????????? 1 4C70E6B4??????? 146???????? 23 4C6545A0 TEST?????? KILLED?? sqlplus@oracle10g (TNS V1-V3)

???????????? 1 4C71FC84??????? 160???????? 17 4C624174 SYS??????? ACTIVE?? sqlplus@oracle10g (TNS V1-V3)

?????????????????

???? 注意：在查詢(xún)中可以看到被殺掉的會(huì)話(huà)的PADDR地址發(fā)生了變化，參照查詢(xún)結(jié)果中的紅色字體。如果多個(gè)session被kill 掉，則多個(gè)session的PADDR

???? 被改為相同的進(jìn)程地址。

? 通過(guò)下面的語(yǔ)句來(lái)找回被kill 掉的ADDR先前的地址

??? SELECT s.username,s.status,

??? x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,

??? decode(bitand (x.ksuprflg,2),,null,1)

??? FROM x$ksupr x,v$session s

??? WHERE s.paddr(+)=x.addr

??? and bitand(ksspaflg,1)!=;??????

???

??? USERNAME?? STATUS?? ADDR?????? KSLLAPSC?? KSLLAPSN KSLLASPO???? ??KSLLID1R KS D

??? ---------- -------- -------- ---------- ---------- ------------ ---------- -- -

?????????????? ACTIVE?? 4C623BB8???????? 99????????? 4 27468?????????????? 275 EV 1

?????????????? ACTIVE?? 4C623040????????? 9???????? 24 27444??????????? ???????? 1

?????????????? ACTIVE?? 4C622A84??????? 101????????? 4 27480?????????????? 274 EV 1

?????????????? ACTIVE?? 4C6224C8????????? 1???????? 48 27450???????????????? ??? 1

?????????????? ACTIVE?? 4C621F0C????????? 1???????? 48 27450??????????????? ???? 1

?????????????? ACTIVE?? 4C6235FC????????? 2????????? 4 27468???????????????? ??? 1

??? SYS??????? ACTIVE?? 4C624174????????? 2???????? 15 27442????????????????

?????????????? ACTIVE?? 4C62081C????????? 1???????? 48 27440???????????????? ??? 1

?????????????? ACTIVE?? 4C621394????????? 1???????? 48 27440???????????????? ??? 1

?????????????? ACTIVE?? 4C620DD8???????? 11???????? 24 27476???????????????? ??? 1

?????????????? ACTIVE?? 4C61F6E8???????? 15????????? 4 27610???????????????? ??? 1

???? ??????????ACTIVE?? 4C620260??????? 222???????? 24 27450???????????????? ??? 1

?????????????? ACTIVE?? 4C61FCA4????????? 7???????? 25 27573???????????????? ??? 1

?????????????? ACTIVE?? 4C61F12C????????? 6???????? 25 27573???????????????? ??? 1

???????? ??????ACTIVE?? 4C61EB70????????? 4???????? 24 27458???????????????? ??? 1

?????????????? ACTIVE?? 4C61E5B4????????? 1???????? 48 27440???????????????? ??? 1

?????????????? ACTIVE?? 4C61DFF8????????? 2???????? 24 27444???????????????? ??? 1

???????????? ???????????4C624730????????? ????????? ??????????????????????

??????????????????????? 4C621950????????? ????????? ??????????????????????

??????????????????????? 4C61DA3C????????? ????????? ??????????????????????

???????????????????

? 或者根據(jù)下面的語(yǔ)句來(lái)獲得發(fā)生變化的addr

??? sys@AUSTIN> select p.addr from v$process p where pid <> 1

????? 2? minus

????? 3? select s.paddr from v$session s;

???

??? ADDR

??? --------

??? 4C621950

??? 4C624730???????????????????

三、在操作系統(tǒng)級(jí)別殺掉會(huì)話(huà)

? 尋找會(huì)話(huà)對(duì)應(yīng)的操作系統(tǒng)的進(jìn)程ID

??? sys@AUSTIN> select SPID from? v$process where ADDR in ('4C621950','4C624730') ;????????

???

??? SPID

??? ----------

??? 27573

??? 27641

???

? 使用kill 命令來(lái)殺掉操作系統(tǒng)級(jí)別進(jìn)程ID

??? kill session -9 27573

???

??? kill session -9 27641

四、獲得當(dāng)前會(huì)話(huà)的SID

? SQL> select userenv('sid') from dual;

? USERENV('SID')

? --------------

???????????? 627

五、多個(gè)會(huì)話(huà)需要kill 的處理辦法

? 1.根據(jù)給定的SID(用戶(hù)名)查找需要殺掉會(huì)話(huà)的信息，包括位于哪一個(gè)實(shí)例

??? set linesize 160

??? col program format a35

??? col username format a18

??? select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session

??? where sid in ('2731','2734','2720','2678','2685')

??? and username='CTICUST'

??? order by inst_id;

???

?????? INST_ID SADDR?????????????????? SID??? SERIAL# PADDR??????????? USERNAME?????????? STATUS?? PROGRAM

??? ---------- ---------------- ---------- ---------- ---------------- ------------------ -------- ---------------------------

???????????? 1 00000003DAF8F870?????? 2678?????? 8265 00000003DBC6CA08 MSS4USR??????????? INACTIVE JDBC Thin Client

???????????? 1 00000003DAF98E48?????? 2685???????? 83 00000003DBC08510 MSS4USR??????????? ACTIVE?? JDBC Thin Client

???????????? 1 00000003DAFC7B80?????? 2720????????? 5 00000003DBBEDA20 MSS4USR??????????? INACTIVE JDBC Thin Client

???????????? 1 00000003DAFD66F8?????? 2731????????? 3 00000003DBBE9AE0 SYS??????????????? ACTIVE? racgimon@svdg0028(TNS V1-V3)

???????????? 1 00000003DAFDA730?????? 2734???????? 15 00000003DBBEC268 MSS4USR??????????? INACTIVE JDBC Thin Client

???????????? 2 00000003DAFD66F8?????? 2731????????? 1 00000003DBBE92F8??????????????????? ACTIVE?? oracle@svdg0029 (ARC0)

???

??? 上面的查詢(xún)中有一個(gè)SID為2731的位于節(jié)點(diǎn)2上。

??? 也可以通過(guò)下面的方式來(lái)獲得RAC的節(jié)點(diǎn)信息，便于確定需要kill 的session究竟位于哪一個(gè)節(jié)點(diǎn)。

???

????? set linesize 160

????? col HOST_NAME format a25

????? SQL> select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUS from gv$instance order by 1;

????? INSTANCE_NUMBER INSTANCE_NAME??? HOST_NAME???????????????? VERSION?????????? STATUS

????? --------------- ---------------- ------------------------- ----------------- ------------

??? ????????????????1 O02WMT1A???????? svd0051????????????????? 10.2.0.4.0??????? OPEN

??????????????????? 2 O02WMT1B???????? svd0052????????????????? 10.2.0.4.0??????? OPEN

??????????????????? 3 O02WMT1C???????? svd0053????????????????? 10.2.0.4.0??????? OPEN

???????????????????

? 2.使用下面查詢(xún)來(lái)生成kill session 的語(yǔ)句

??? select 'alter system kill session '''|| sid ||',' ||SERIAL# ||''''||';'? from ?gv$session

??? where sid in ('2731','2734','2720','2678','2685')

??? order by inst_id;

?????

??? 獲得下列kill session的語(yǔ)句，根據(jù)要求由于此次需要殺掉的session全部位于節(jié)點(diǎn)1，因此登錄到節(jié)點(diǎn)節(jié)點(diǎn)1執(zhí)行下面的語(yǔ)句??

????????

??? alter system kill session '2678,8265';

???

??? alter system kill session '2685,83';

???

??? alter system kill session '2720,5';?

???

??? alter system kill session '2731,3';

????

??? alter system kill session '2734,15';

????

??? alter system kill session '2731,1';??? --此條命令不需要執(zhí)行，該session位于節(jié)點(diǎn)2。

????????????

六、有關(guān)kill session的參考：

? Oracle How To:如何快速殺死占用過(guò)多資源(CPU,內(nèi)存)的數(shù)據(jù)庫(kù)進(jìn)程? ?

? Oracle中Kill session的研究

? Killing Oracle Sessions

七、更多參考：

有關(guān)性能優(yōu)化請(qǐng)參考

Oracle 硬解析與軟解析

共享池的調(diào)整與優(yōu)化(Shared pool Tuning)

Buffer cache 的調(diào)整與優(yōu)化(一)

Oracle 表緩存(caching table)的使用

有關(guān)閃回特性請(qǐng)參考

Oracle 閃回特性(FLASHBACK DATABASE)

Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 閃回特性(Flashback Query、Flashback Table)

Oracle 閃回特性(Flashback Version、Flashback Transaction)

有關(guān)基于用戶(hù)管理的備份和備份恢復(fù)的概念請(qǐng)參考

Oracle 冷備份

Oracle 熱備份

Oracle 備份恢復(fù)概念

Oracle 實(shí)例恢復(fù)

Oracle 基于用戶(hù)管理恢復(fù)的處理(詳細(xì)描述了介質(zhì)恢復(fù)及其處理)

有關(guān)RMAN的恢復(fù)與管理請(qǐng)參考

RMAN 概述及其體系結(jié)構(gòu)

RMAN 配置、監(jiān)控與管理

RMAN 備份詳解

RMAN 還原與恢復(fù)

原文鏈接： http://blog.csdn.net/robinson_0612/article/details/6439019

轉(zhuǎn)載于:https://my.oschina.net/dtec/blog/47358

總結(jié)

以上是生活随笔為你收集整理的Oracle 彻底 kill session的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。