如何终止连接到我的 oracle 数据库的所有会话？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/55342/
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 can I kill all sessions connecting to my oracle database?
提问by BIBD
I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.
我需要在没有管理员监督的情况下快速（并且强制）终止所有连接到我的 oracle 数据库的外部会话。
I don't want to just lock the database and let the users quit gracefully.
我不想只是锁定数据库并让用户优雅地退出。
How would I script this?
我将如何编写此脚本？
回答by BIBD
This answer is heavily influenced by a conversation here: http://www.tek-tips.com/viewthread.cfm?qid=1395151&page=3
此答案深受此处对话的影响：http: //www.tek-tips.com/viewthread.cfm?qid=1395151& page=3
ALTER SYSTEM ENABLE RESTRICTED SESSION;
begin
for x in (
select Sid, Serial#, machine, program
from v$session
where
machine <> 'MyDatabaseServerName'
) loop
execute immediate 'Alter System Kill Session '''|| x.Sid
|| ',' || x.Serial# || ''' IMMEDIATE';
end loop;
end;
I skip killing sessions originating on the database server to avoid killing off Oracle's connections to itself.
我跳过终止源自数据库服务器的会话以避免终止 Oracle 与自身的连接。
回答by Gaius
As SYS:
作为 SYS：
startup force;
Brutal, yet elegant.
粗犷，却又不失优雅。
回答by Gaius
Before killing sessions, if possible do
在杀死会话之前，如果可能的话
ALTER SYSTEM ENABLE RESTRICTED SESSION;
to stop new sessions from connecting.
阻止新会话连接。
回答by Thomas Bratt
I've been using something like this for a while to kill my sessions on a shared server. The first line of the 'where' can be removed to kill all non 'sys' sessions:
我一直在使用这样的东西来终止我在共享服务器上的会话。可以删除“where”的第一行以终止所有非“sys”会话：
BEGIN
FOR c IN (
SELECT s.sid, s.serial#
FROM v$session s
WHERE (s.Osuser = 'MyUser' or s.MACHINE = 'MyNtDomain\MyMachineName')
AND s.USERNAME <> 'SYS'
AND s.STATUS <> 'KILLED'
)
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || '''';
END LOOP;
END;
回答by Vadzim
Additional info
附加信息
Important Oracle 11g changes to alter session kill session
Oracle author Mladen Gogala notes that an @ sign is now required to kill a session when using the inst_id column:
重要的 Oracle 11g 更改以更改会话终止会话
Oracle 作者 Mladen Gogala 指出，现在在使用 inst_id 列时需要 @ 符号来终止会话：
alter system kill session '130,620,@1';
回答by Gazmo
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
如果您想阻止新用户连接，但允许当前会话继续直到它们处于非活动状态，您可以将数据库置于 QUIESCE 模式：
ALTER SYSTEM QUIESCE RESTRICTED;
From the Oracle Database Administrator's Guide:
Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active...Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state
非 DBA 活动会话将一直持续到它们变为非活动状态。活动会话是当前在事务、查询、获取或 PL/SQL 语句中的会话；或当前持有任何共享资源（例如，入队）的会话。不允许非活动会话变为活动状态...一旦所有非 DBA 会话变为非活动状态，ALTER SYSTEM QUIESCE RESTRICTED 语句完成，并且数据库处于停顿状态
回答by jon077
I found the below snippet helpful. Taken from: http://jeromeblog-jerome.blogspot.com/2007/10/how-to-unlock-record-on-oracle.html
我发现以下代码段很有帮助。摘自：http: //jeromeblog-jerome.blogspot.com/2007/10/how-to-unlock-record-on-oracle.html
select
owner||'.'||object_name obj ,
oracle_username||' ('||s.status||')' oruser ,
os_user_name osuser ,
machine computer ,
l.process unix ,
s.sid||','||s.serial# ss ,
r.name rs ,
to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l ,
dba_objects o ,
v$session s ,
v$transaction t ,
v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj
;
Then ran:
然后跑：
Alter System Kill Session '<value from ss above>'
;
To kill individual sessions.
杀死单个会话。
回答by Grrey
Try trigger on logon
登录时尝试触发
Insted of trying disconnect users you should not allow them to connect.
Insted 尝试断开用户连接，您不应该允许他们连接。
There is and example of such trigger.
有这样的触发器的例子。
CREATE OR REPLACE TRIGGER rds_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
IF (to_number(to_char(sysdate,'HH24'))< 6) and (to_number(to_char(sysdate,'HH24')) >18) THEN
RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');
END IF;
END;
回答by dovka
To answer the question asked, here is the most accurate SQL to accomplish the job, you can combine it with PL/SQL loop to actually run kill statements:
为了回答所提出的问题，这里是完成这项工作的最准确的 SQL，您可以将它与 PL/SQL 循环结合起来实际运行 kill 语句：
select ses.USERNAME,
substr(MACHINE,1,10) as MACHINE,
substr(module,1,25) as module,
status,
'alter system kill session '''||SID||','||ses.SERIAL#||''';' as kill
from v$session ses LEFT OUTER JOIN v$process p ON (ses.paddr=p.addr)
where schemaname <> 'SYS'
and not exists
(select 1
from DBA_ROLE_PRIVS
where GRANTED_ROLE='DBA'
and schemaname=grantee)
and machine!='yourlocalhostname'
order by LAST_CALL_ET desc;
回答by Ramki
If Oracle is running in Unix /Linux then we can grep for all client connections and kill it
如果 Oracle 在 Unix /Linux 中运行，那么我们可以为所有客户端连接 grep 并杀死它
grep all oracle client process:
grep所有oracle客户端进程：
ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | wc -l
ps -ef | grep 本地=否 | grep -v grep | awk '{print $2}' | wc -l
Kill all oracle client process :
杀死所有 oracle 客户端进程：
kill -9 ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'
杀 -9 ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'
