How can I kill all sessions connecting to my oracl

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?

Answer 1

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 2

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;

Answer 3

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 4

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}'