如何终止连接到oracle数据库的所有会话?

这一答案深受以下对话的影响: 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;

我跳过终止数据库服务器上发起的会话,以避免终止Oracle与自身的连接。

作为系统:

startup force;

在终止会话之前,如果可能,请执行以下操作

ALTER SYSTEM ENABLE RESTRICTED SESSION;

阻止新会话连接。

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;

如果要阻止新用户连接,但允许当前会话继续,直到它们处于非活动状态,则可以将数据库置于静止模式:

ALTER SYSTEM QUIESCE RESTRICTED;

从 Oracle Database Administrator's Guide

非DBA活动会话将继续 直到他们变得不活跃。活跃的 会话是当前正在进行的会话 在一个事务、一个查询、一个 当前正在举行任何活动的会话 排队)。不支持非活动会话 允许变为活动状态…一劳永逸 改变系统静止受限

更改会话终止会话的重要Oracle 11g更改

甲骨文作者Mladen Gogala指出,现在需要一个@符号来 使用inst_id列时终止会话:

alter system kill session '130,620,@1';

http://www.dba-oracle.com/tips_killing_oracle_sessions.htm

登录时尝试触发

不要试图断开用户的连接,你不应该允许他们连接。

这类触发器有很多例子。

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;

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
;

Alter System Kill Session '<value from ss above>'
;

终止单个会话。

下面是完成这项工作的最精确的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;

如果Oracle在Unix/Linux中运行,那么我们可以对所有客户端连接进行grep并将其删除

grep所有oracle客户端进程:

ps-ef | grep LOCAL=NO | grep-v grep | awk{print$2}| wc-l

终止所有oracle客户端进程:

杀死-9 ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'