我需要在没有管理员的监督下快速(强行)终止所有连接到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与其自身的连接。

回答

尝试在登录时触发

为了尝试断开用户连接，我们不应该允许他们连接。

有这样的触发器的例子。

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;

回答

在终止会话之前，如果可能的话，请执行

ALTER SYSTEM ENABLE RESTRICTED SESSION;

阻止新会话连接。

回答

如果要停止新用户连接，但允许当前会话继续进行直到它们处于非活动状态，则可以将数据库置于QUIESCE模式：

ALTER SYSTEM QUIESCE RESTRICTED;

从《 Oracle数据库管理员指南》中：

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

回答

我发现以下代码段很有帮助。摘自：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>'
;

杀死单个会话。

回答

作为SYS：

startup force;

残酷而优雅。