How to kill all active and inactive oracle sessions for user

Question

I am trying the below script to kill all active and inactive oracle sessions for user at once but it doesn t work  The script executes successfully but does not kill sessions for user   BEGIN   FOR r IN  select sid serial  from v session where username    USER     LOOP     EXECUTE IMMEDIATE  alter system kill session        r sid                  r serial             END LOOP  END

User · Accepted Answer

The KILL SESSION command doesn t actually kill the session  It merely asks the session to kill itself  In some situations  like waiting for a reply from a remote database or rolling back transactions  the session will not kill itself immediately and will wait for the current operation to complete  In these cases the session will have a status of  quot marked for kill quot   It will then be killed as soon as possible  Check the status to confirm  SELECT sid  serial   status  username FROM v session   You could also use IMMEDIATE clause  ALTER SYSTEM KILL SESSION  sid serial   IMMEDIATE   The IMMEDIATE clause does not affect the work performed by the command  but it returns control back to the current session immediately  rather than waiting for confirmation of the kill  Have a look at Killing Oracle Sessions  Update If you want to kill all the sessions  you could just prepare a small script  SELECT  ALTER SYSTEM KILL SESSION      sid       serial       IMMEDIATE   FROM v session   Spool the above to a  sql file and execute it  or  copy paste the output and run it

User · Answer

BEGIN   FOR r IN  select sid serial  from v session where username  user     LOOP       EXECUTE IMMEDIATE  alter system kill session        r sid                     r serial         immediate     END LOOP  END    This should work - I just changed your script to add the immediate keyword  As the previous answers pointed out  the kill session only marks the sessions for killing  it does not do so immediately but later when convenient   From your question  it seemed you are expecting to see the result immediately  So immediate keyword is used to force this

User · Answer

For Oracle 11g this may not work as you may receive an error like below  Error report  SQL Error  ORA-00026  missing or invalid session ID 00026  00000 -   missing or invalid session ID   Cause     Missing or invalid session ID string for ALTER SYSTEM KILL SESSION   Action    Retry with a valid session ID    To rectify this  use below code to identify the sessions  SQL gt  select inst id sid serial  from gv session or v session   NOTE   v session do not have inst id field  and Kill them using  alter system kill session  sid serial  inst id  IMMEDIATE

User · Answer

Execute this script   SELECT  ALTER SYSTEM KILL SESSION      sid       serial       IMMEDIATE    FROM v session  where username  YOUR USER     It will printout sqls  which should be executed

User · Answer

BEGIN   FOR r IN  select sid serial  from v session where username  user     LOOP       EXECUTE IMMEDIATE  alter system kill session        r sid            r serial             END LOOP  END         It works for me

User · Answer

inactive session the day before kill    x000D   x000D  begin x000D      for i in  select   from v session where status  INACTIVE  and  sysdate-PREV EXEC START  gt 1  x000D      LOOP x000D          EXECUTE IMMEDIATE q  ALTER SYSTEM KILL SESSION      i sid  q         i serial   q         IMMEDIATE    x000D      END LOOP  x000D  end  x000D   x000D   x000D

[oracle] How to kill all active and inactive oracle sessions for user

Examples related to oracle

Examples related to session

Examples related to stored-procedures

Examples related to schema