如何为用户杀死所有活动和非活动的oracle会话 [英] How to kill all active and inactive oracle sessions for user

问题描述

我正在尝试使用以下脚本一次杀死用户的所有活动和非活动oracle会话，但是它不起作用.该脚本可以成功执行，但不会终止用户的会话.

I am trying the below script to kill all active and inactive oracle sessions for user at once but it doesn't work. The script executes successfully but does not kill sessions for user.

BEGIN
  FOR r IN (select sid,serial# from v$session where username = 'USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 
      || ',' || r.serial# || '''';
  END LOOP;
END;

推荐答案

KILL SESSION命令实际上不会 kill 该会话.它只是要求会话杀死自己.在某些情况下，例如等待来自远程数据库的答复或回滚事务，会话将不会立即终止自身，而将等待当前操作完成.在这些情况下，会话的状态将为"标记为杀死".然后它将被尽快杀死.

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

检查状态以确认:

SELECT sid, serial#, status FROM v$session;

您还可以使用 IMMEDIATE 子句:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

IMMEDIATE子句不会影响命令执行的工作，但会立即将控制权返回给当前会话，而不是等待终止的确认.看看杀死Oracle会话.

The IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. Have a look at Killing Oracle Sessions.

更新.如果您想终止所有会话，可以准备一个小脚本.

Update If you want to kill all the sessions, you could just prepare a small script.

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;

将上面的

假脱机到.sql文件并执行，或者复制粘贴输出并运行.

Spool the above to a .sql file and execute it, or, copy paste the output and run it.

这篇关于如何为用户杀死所有活动和非活动的oracle会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！