kill session之後,會話一直無法釋放的巧妙解決辦法(lsof)
我的資料庫版本:
SQL> select * from v$version ;
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
下面這些非法連線,需要被kill掉:
SQL> select 'alter system kill session '''||sid||','||serial#||'''; ',client_info,program,status
2 from v$session where client_info like '132.224.36.10';
'ALTERSYSTEMKILLSESSION'''||SI CLIENT_INFO PROGRAM STATUS
------------------------------------------ -------------- -------------------------- --------
alter system kill session '1365,1937'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1367,17258'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1383,7729'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1402,3818'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1638,4784'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1982,7247'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '2004,4734'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
------------------------------------------ -------------- -------------------------- --------
alter system kill session '1365,1937'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1367,17258'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1383,7729'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1402,3818'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1638,4784'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '1982,7247'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
alter system kill session '2004,4734'; 132.224.36.10 oracle@basdb1 (TNS V1-V3) INACTIVE
為避免出現kill session之後,會話長期存在,先儲存一下spid,也方便與後面的方法做對比
一般在10g之後,不會出現session被kill之後,在v$process中無法找到spid的情況,但實際上,我又反覆碰到了
SQL> select spid from v$process where addr in
2 (select paddr from v$session where client_info='132.224.36.10') ;
SPID
------------
15843
16581
13466
16862
8224
7332
12161
------------
15843
16581
13466
16862
8224
7332
12161
7 rows selected
利用上面的指令碼殺掉下面的會話:
alter system kill session '1365,1937';
alter system kill session '1367,17258';
alter system kill session '1383,7729';
alter system kill session '1402,3818';
alter system kill session '1638,4784';
alter system kill session '1982,7247';
alter system kill session '2004,4734';
結果KILLED狀態的會話一直不釋放,查詢v$process也找不到對應的spid
無法通過在server端kill spid來快速釋放連線了
SQL> select sid,serial#,client_info,status from v$session where status='KILLED';
SID SERIAL# CLIENT_INFO STATUS
---------- ---------- ------------------------------ --------
1365 1937 132.224.36.10 KILLED
1367 17258 132.224.36.10 KILLED
1383 7729 132.224.36.10 KILLED
1402 3818 132.224.36.10 KILLED
1638 4784 132.224.36.10 KILLED
1982 7247 132.224.36.10 KILLED
2004 4734 132.224.36.10 KILLED
---------- ---------- ------------------------------ --------
1365 1937 132.224.36.10 KILLED
1367 17258 132.224.36.10 KILLED
1383 7729 132.224.36.10 KILLED
1402 3818 132.224.36.10 KILLED
1638 4784 132.224.36.10 KILLED
1982 7247 132.224.36.10 KILLED
2004 4734 132.224.36.10 KILLED
7 rows selected
SQL> select spid from v$process where addr in
2 (select paddr from v$session where status='KILLED') ;
2 (select paddr from v$session where status='KILLED') ;
SPID
------------
------------
SQL>
SQL> select paddr from v$session where status='KILLED' ;
SQL> select paddr from v$session where status='KILLED' ;
PADDR
----------------
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
----------------
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
7 rows selected
因為知道IP地址等資訊,我們可以通過lsof工具,根據netstat的埠情況來判定spid
下面給出的就是這個方法:
crmdb2:/oracle>netstat -an | grep tcp | grep 132.224.36.10 |grep ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55190 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55040 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55004 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55163 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55065 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.54982 ESTABLISHED
tcp 0 0 130.34.3.10.1521 132.224.36.10.55227 ESTABLISHED
crmdb2:/oracle>/usr/local/bin/lsof -i tcp:54982 |grep 132.224.36.10
oracle 16862 oracle 22u IPv4 0xe000000b88c31a00 0t14832 TCP crmdb2_vip:1521->132.224.36.10:54982 (ESTABLISHED)
oracle 16862 oracle 22u IPv4 0xe000000b88c31a00 0t14832 TCP crmdb2_vip:1521->132.224.36.10:54982 (ESTABLISHED)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10867315/viewspace-713606/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-00031: 標記要刪去的會話,但會話kill不掉解決辦法會話
- IIS 之 無法找到該頁 解決辦法
- 【會話】Oracle kill session系列會話OracleSession
- 關於網路重置後無法聯網的解決辦法
- Win10升級之後提示系統無法啟用的解決辦法Win10
- 檔案無法粉碎解決辦法
- 解決Fiddler無法抓到手機的會話包會話
- MongoDB Drop集合不釋放磁碟空間的解決辦法MongoDB
- 電腦休眠後無法喚醒怎麼辦?win10電腦休眠後無法喚醒的解決辦法Win10
- 360打補丁後系統無法啟動的解決辦法
- 新浪UC無法登入的解決辦法
- 無法訪問google picasa的解決辦法Go
- 解決php的session無法儲存到redisPHPSessionRedis
- iPhone突然無法充電的解決辦法 iPhone無法充電怎麼辦iPhone
- LINUX 安裝python3 命令之後 python無法使用的解決辦法LinuxPython
- activemq無法啟動且後臺管理介面進不去的解決辦法MQ
- windows無法配置此無線連線的解決辦法Windows
- jvm:jmap無法dump檔案的解決辦法JVM
- 登錄檔無法開啟的解決辦法
- 印表機無法列印的原因與解決辦法
- Xamarin Android SDK無法更新的解決辦法Android
- session丟失與解決辦法的資料Session
- Session莫名丟失的原因及解決辦法Session
- Session丟失的解決辦法小結 (轉)Session
- Ucenter後臺登陸驗證碼CCCC的解決方法無法登入解決辦法
- Oracle中的Session kill不釋放問題OracleSession
- android中HttpClient獲取Session然後使用 WebView共享session的解決辦法(轉)AndroidHTTPclientSessionWebView
- Oracle statspack無法收集快照,及解決辦法Oracle
- 批量kill殺死某些會話session的plsql會話SessionSQL
- 無法建立目錄或檔案 無法建立目錄的解決辦法
- Jenkins解決無法獲取外掛的辦法Jenkins
- 隨身碟無法停止通用卷的解決辦法
- Live mesh 在xp無法安裝的解決辦法
- discuz資料庫搬家,改密碼後無法訪問解決辦法資料庫密碼
- 升級Win10後螢幕一直閃爍的解決辦法Win10
- windows下處理kill session不被釋放的鎖WindowsSession
- 記vscode無法啟動解決辦法VSCode
- windows7無法使用無線路由器的解決辦法Windows路由器