[20211129]完善tpt killi.sql指令碼.txt

[20211129]完善tpt killi.sql指令碼.txt

--//原始指令碼僅僅kill單個例項的相關程式，修改可以工作在多個例項上的指令碼。

$ cp killi.sql killix.sql
$ cat killix.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------
--
-- File name:   kill.sql
-- Purpose:     Generates commands for killing selected sessions
--
-- Author:      Tanel Poder
-- Copyright:   (c)
--
-- Usage:       @kill <filter expression> (example: @kill username='SYSTEM')
--              @kill sid=150
--              @kill username='SYSTEM'
--              @kill "username='APP' and program like 'sqlplus%'"
--
-- Other:       This script doesnt actually kill any sessions
--              it just generates the ALTER SYSTEM KILL SESSION
--              commands, the user can select and paste in the selected
--              commands manually
--
--------------------------------------------------------------------------------

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate -- '
       ||username||'@'||machine||' ('||program||');' commands_to_verify_and_run
from gv$session
where &1
and sid != (select sid from v$mystat where rownum = 1)
/

--//在測試環境測試看看：

1.環境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ i
USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  book                 xxxxxdg4                    1 44    49       11.2.0.4.0 20211129 57178      27    57177           000000008638EC10 000000008620F338

2.開啟新的會話：

SCOTT@book> @ killix "sid=44"
COMMANDS_TO_VERIFY_AND_RUN
----------------------------------------------------------------------------------------------
alter system kill session '44,49,@1' immediate -- SYS@xxxxxdg4 (sqlplus@xxxxxdg4 (TNS V1-V3));

SCOTT@book> alter system kill session '44,49,@1' immediate -- SYS@xxxxxdg4 (sqlplus@xxxxxdg4 (TNS V1-V3));
System altered.

--//切換回去，執行：
SYS@book> @ ver1
select dbms_utility.port_string port_string, version,v$version.* from v$instance,v$version where rownum<=1
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 57178
Session ID: 44 Serial number: 49

--//連線已經斷開。