How can I kill all sessions connecting to my oracle database?

Question

I need an sqlplus script to quickly kill off all external sessions connecting to my oracle database without the supervision of and administrator.

Answer 1

This answer is heavily influenced by a convseration here: http://www.tek-tips.com/viewthread.cfm?qid=1395151&page=3

begin     
    ALTER SYSTEM ENABLE RESTRICTED SESSION;

    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.

Answer 2

Try trigger on logon

Insted of trying disconnect users you should not allow them to connect.

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;

Answer 3

Before killing sessions, if possible do

ALTER SYSTEM ENABLE RESTRICTED SESSION;

to stop new sessions from connecting.

Answer 4

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:

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

Answer 5

I found the below snippet helpful. Taken from: 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.

Answer 6

I recenlty published a script to Kill all Oracle Sessions on my blog that you can check out. The key is to using the Alter System Kill Session syntax in a loop.

Answer 7

As SYS:

startup force;

Brutal, yet elegant.