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

User · Accepted Answer

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.

User · Answer

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   lt value from ss above gt       To kill individual sessions

User · Answer

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

User · Answer

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    lt  6  and  to number to char sysdate  HH24     gt 18  THEN     RAISE APPLICATION ERROR -20005  Logon only allowed during business hours      END IF   END

User · Answer

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  lt  gt   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

User · Answer

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

User · Answer

Before killing sessions  if possible do  ALTER SYSTEM ENABLE RESTRICTED SESSION    to stop new sessions from connecting

User · Answer

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

User · Answer

As SYS   startup force    Brutal  yet elegant

User · Answer

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  lt  gt   SYS        AND s STATUS  lt  gt   KILLED        LOOP       EXECUTE IMMEDIATE  alter system kill session        c sid           c serial             END LOOP  END

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

Examples related to oracle

Examples related to session

Examples related to sqlplus

Examples related to kill

Examples related to database-administration