[20230519]建立完善cancelx.sql脚本.txt

--//tpt 有一个cancel.sql脚本用于取消正在运行的sql语句,该版本仅仅支持单实例,自己写一个cancelx.sql支持rac环境.

$ cat cancelx.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:   cancel.sql
-- Purpose:     Generates commands for canceling selected sessions
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--              
-- Usage:       @cancel <filter expression> (example: @cancel username='SYSTEM')
--              @cancel sid=150
--              @cancel username='SYSTEM'
--              @cancel "username='APP' and program like 'sqlplus%'"
--
-- Other:       Oracle 12.2 or newer required!
--
--              This script doesnt actually cancel any sessions       
--              it just generates the ALTER SYSTEM CANCEL SQL
--              commands, the user can select and paste in the selected
--              commands manually
--
--              See more info at:
--                https://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/
--              
--              Oracle 12.2 and 18c take SID,SERIAL#[,@inst_id,sql_id] as arguments.
--              Just SID,SERIAL# are enough for canceling the currently running SQL
--
--              An older resource manager technique that doesn't always seem to work (not fast enough at least):
--
--              EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid, serial#, 'CANCEL_SQL');
--
--------------------------------------------------------------------------------

SELECT 'ALTER SYSTEM CANCEL SQL '''||sid||','||serial#||',@'||inst_id||''' -- '
       ||username||' ['||NVL(sql_id, 'sql_id is null')||'] @'||machine||' ('||program||');' commands_to_verify_and_run
FROM gv$session
WHERE &1
AND sid != (SELECT sid FROM v$mystat WHERE ROWNUM = 1)
/