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

Question

I need to quickly (and forcibly) kill off all external sessions connecting to my oracle database without the supervision of and administrator.

I don't want to just lock the database and let the users quit gracefully.

How would I script this?

score 46 · Accepted Answer · edited Jun 10 '13 at 15:51

This answer is heavily influenced by a conversation here: 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.

I get ORA-06550. ALTER SYSTEM seems not allowed inside begin end — bernd_k, Sep 28 '11 at 07:04

score 12 · Answer 2 · answered Sep 29 '10 at 19:45

12

As SYS:

startup force;

Brutal, yet elegant.

answered Sep 29 '10 at 19:45

Gaius

2,556
1
24
43

score 7 · Answer 3 · answered Sep 13 '08 at 20:00

7

Before killing sessions, if possible do

ALTER SYSTEM ENABLE RESTRICTED SESSION;

to stop new sessions from connecting.

answered Sep 13 '08 at 20:00

Yes indeed, this does make my solution more complete. – BIBD Sep 15 '08 at 14:23
Important! Do not forget to disable the restricted mode before disconnect – manix Jun 02 '21 at 19:10

score 5 · Answer 4 · answered Oct 18 '11 at 10:55

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:

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;

score 2 · Answer 5 · answered Oct 24 '08 at 16:10

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

I'll keep that in mind. In my case I wanted to boot the users off without waiting for their transactions to finish (some of them are rather long). Everybody out of the pool NOW!!! — BIBD, Oct 27 '08 at 16:19

score 2 · Answer 6 · answered Feb 02 '12 at 18:14

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:

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

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

score 1 · Answer 7 · answered Sep 12 '08 at 17:34

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;

This would indeed work if my goal was to limit access to specific IP ranges or times. My goal was to force all my users off to database, not prevent logins. Thanks, though - I'll keep that in mind. — BIBD, Sep 15 '08 at 14:22

score 1 · Answer 8 · edited May 25 '11 at 16:22

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:

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;

score 1 · Answer 9 · answered Jun 02 '09 at 14:05

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.

score 0 · Answer 10 · answered Mar 30 '16 at 04:47

If Oracle is running in Unix /Linux then we can grep for all client connections and kill it

grep all oracle client process:

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

Kill all oracle client process :

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

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

10 Answers10

Linked