[20211014]如何取消用户的查询在另外的会话.txt

[20211014]如何取消用户的查询在另外的会话.txt --//昨天看了链接https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ --//里面提到几种方法取消用户的查询在另外的会话. --//当然最简单的方法,对于当前会话就是按ctrl+c,仅仅对linux有效,windows直接退出,如果是12c以上的版本: ALTER SYSTEM CANCEL SQL 'SID, SERIAL; ALTER SYSTEM CANCEL SQL 'SID, SERIAL, SQL_ID'; ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID'; --//作者还提到了设置consumer group对会话取消正在进行的查询: DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS   sessionid IN NUMBER,   sessionserial IN NUMBER,   consumergroup IN VARCHAR2 ) --//要测试它需要建立consumer group,很少这方面的实践. --//作者还提到一种方法就是发送SIGURG给对应进程,不过也是仅仅对linux系统有效,测试看看. 1.环境: SCOTT@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 2.测试: SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------         30        965 64784                    DEDICATED 64785       26         86 alter system kill session '30,965' immediate; SCOTT@book> select count(*) from all_objects,all_objects; $ kill -l  1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL  5) SIGTRAP      6) SIGABRT      7) SIGBUS       8) SIGFPE  9) SIGKILL     10) SIGUSR1     11) SIGSEGV     12) SIGUSR2 13) SIGPIPE     14) SIGALRM     15) SIGTERM     16) SIGSTKFLT 17) SIGCHLD     18) SIGCONT     19) SIGSTOP     20) SIGTSTP 21) SIGTTIN     22) SIGTTOU     23) SIGURG      24) SIGXCPU ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 25) SIGXFSZ     26) SIGVTALRM   27) SIGPROF     28) SIGWINCH 29) SIGIO       30) SIGPWR      31) SIGSYS      34) SIGRTMIN 35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3  38) SIGRTMIN+4 39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8 43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13 48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7  58) SIGRTMAX-6 59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2 63) SIGRTMAX-1  64) SIGRTMAX --//UGR=23,URG应该是urgent缩写. $ kill -23 64785 SCOTT@book> select count(*) from all_objects,all_objects; select count(*) from all_objects,all_objects                      * ERROR at line 1: ORA-01013: user requested cancel of current operation --//仅仅取消,但是进程并没有退出. SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------         30        965 64784                    DEDICATED 64785       26         86 alter system kill session '30,965' immediate; 3.客户是windows呢? --//按照作者的介绍是不行的,windows直接忽略URG信号.实际上windows下ctrl+c也不行,直接退出.大家可以自行测试. 4.转抄一些主要内容: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ Before we go on, here's how query cancellation (pressing CTRL+C in sqlplus for example) works in Oracle: 在我们继续之前，下面是查询取消(例如在Ctll+c中按Cqlplus键)在Oracle中的工作原理： The user presses CTRL+C or clicks some button in App which runs OCICancel() 用户按CTRL+C或点击运行OCICancel()的应用程序中的一些按钮 The client process sends an urgent TCP packet (which is a regular TCP packet with URG bit set) to the socket in the database server it is connected to 客户端进程将一个紧急TCP包(这是一个设置了URG位的常规TCP包)发送到它所连接到的数据库服务器中的套接字 The server OS TCP stack receives the urgent TCP packet and sends URGENT signal to the process which owns the socket (the Oracle server process) 服务器OSTCP堆栈接收紧急TCP数据包，并向拥有套接字的进程(Oracle服务器进程)发送紧急信号 Unix interrupts whatever the current process was doing and calls the URGENT signal handler function (which Oracle has registered during process startup) Unix中断当前进程正在做的任何事情，并调用紧急信号处理程序函数(Oracle在进程启动过程中注册) The urgent signal handler blindly assumes that the urgent signal has been received because user wants to cancel the query, stops the execution and returns back with an error: ORA-01013: user requested cancel of current operation 紧急信号处理程序盲目地假设已经收到了紧急信号，因为用户想要取消查询，停止执行并返回一个错误：ORA-01013：用户请求取消当前 操作 This works only on Unix platforms. Also this does not work when your client application is Windows sqlplus! This is because Windows sqlplus does not set up the out-of-band break checking properly when connecting. Maybe this is because old Windows versions TCP stacks didn't know anything about urgent TCP packets! :) 这只在Unix平台上工作。此外，当您的客户端应用程序是Windows sqlplus！这是因为Windows sqlplus在连接时没有正确设置带外中断检 查。也许这是因为旧的Windows版本的TCP堆栈对紧急TCP数据包一无所知！：)