如何杀死用户的所有活动和非活动 oracle 会话
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31201693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to kill all active and inactive oracle sessions for user
提问by Andrew
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.
我正在尝试使用以下脚本一次终止用户的所有活动和非活动 oracle 会话，但它不起作用。该脚本成功执行但不会终止用户的会话。
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;
回答by Lalit Kumar B
The KILL SESSIONcommand doesn't actually killthe 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.
该KILL SESSION命令实际上不杀了会议。它只是要求会话杀死自己。在某些情况下，例如等待来自远程数据库的回复或回滚事务，会话不会立即自行终止，而是会等待当前操作完成。在这些情况下，会话将具有“标记为终止”的状态。然后它会尽快被杀死。
Check the status to confirm:
检查状态以确认：
SELECT sid, serial#, status FROM v$session;
You could also use IMMEDIATEclause:
您还可以使用IMMEDIATE子句：
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
The IMMEDIATEclause 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.
该IMMEDIATE子句不影响命令执行的工作，但会立即将控制权返回给当前会话，而不是等待终止确认。看看杀死 Oracle 会话。
UpdateIf 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;
Spoolthe above to a .sqlfile and execute it, or, copy paste the output and run it.
将上述内容假脱机到一个.sql文件并执行它，或者，复制粘贴输出并运行它。
回答by Zeeshan Adnan
BEGIN
FOR r IN (select sid,serial# from v$session where username='user')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ','
|| r.serial# || ''' immediate';
END LOOP;
END;
This should work - I just changed your script to add the immediatekeyword. As the previous answers pointed out, the kill sessiononly marks the sessions for killing; it does not do so immediately but later when convenient.
这应该有效 - 我只是更改了您的脚本以添加immediate关键字。正如前面的答案所指出的，kill session唯一标记为杀死会话；它不会立即执行，而是在方便时稍后执行。
From your question, it seemed you are expecting to see the result immediately. So immediatekeyword is used to force this.
从您的问题来看，您似乎希望立即看到结果。所以immediate关键字用于强制执行此操作。
回答by elkoo
Execute this script:
执行这个脚本：
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'
FROM v$session
where username='YOUR_USER';
It will printout sqls, which should be executed.
它将打印出应该执行的 sqls。
回答by Sid133
For Oracle 11g this may not work as you may receive an error like below
对于 Oracle 11g，这可能不起作用，因为您可能会收到如下错误
Error report:
SQL Error: ORA-00026: missing or invalid session ID
00026. 00000 - "missing or invalid session ID"
*Cause: Missing or invalid session ID string for ALTER SYSTEM KILL SESSION.
*Action: Retry with a valid session ID.
To rectify this, use below code to identify the sessions
要纠正此问题，请使用以下代码来识别会话
SQL> select inst_id,sid,serial# from gv$sessionor v$session
SQL> select inst_id,sid,serial# from gv$session或 v$session
NOTE: v$session do not have inst_id field
注意：v$session 没有 inst_id 字段
and Kill them using
并使用杀死它们
alter system kill session 'sid,serial,@inst_id' IMMEDIATE;
回答by chandan gupta
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;
/
It works for me.
这个对我有用。
回答by Faruk ?EV?K
inactive session the day before kill
杀死前一天的非活动会话
begin
for i in (select * from v$session where status='INACTIVE' and (sysdate-PREV_EXEC_START)>1)
LOOP
EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]' ||i.serial#||q'[']'||' IMMEDIATE');
END LOOP;
end;