[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 http://tanelpoder.com -- 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) http://www.tanelpoder.com -- -- 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 --//连接已经断开。