How to kill all active and inactive oracle sessions for the user

Question

I am trying to use the below script to kill all active and inactive oracle sessions for the user right away, but this will not work. The script runs successfully, but does not kill sessions for the 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; 

Answer 1

The KILL SESSION command does not actually end the session. He just asks the session to kill himself. In some situations, such as waiting for a response from a remote database or rollback transactions, the session will not exit immediately and will wait for the current operation to complete. In these cases, the session will have the status “ marked for destruction ”. Then he will be killed as soon as possible.

Check the status to confirm:

 SELECT sid, serial#, status FROM v$session; 

You can also use the IMMEDIATE clause:

 ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

The IMMEDIATE clause does not affect the work performed by the command, but immediately returns control back to the current session, rather than waiting for confirmation of destruction. Take a look at killing Oracle sessions .

Update If you want to kill all sessions, you can simply prepare a small script.

 SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' FROM v$session; 

Sign the above into a .sql file and execute it.

Answer 2

 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 modified your script to add the immediate keyword. As pointed out in previous answers, kill session only marks kill session sessions; he does not do it immediately, but later, when it is convenient.

From your question, it looks like you expect to see the result immediately. Therefore, the immediate keyword is used for this.

Answer 3

Run this script:

 SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where username='YOUR_USER'; 

The sqls will be printed, which must be executed.

Answer 4

For Oracle 11g, this may not work, as you may receive an error message as shown 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 fix this, use the code below to identify sessions

SQL> select inst_id,sid,serial# from gv$session or v $ session

NOTE : v $ session does not have inst_id field

and kill them using

 alter system kill session 'sid,serial,@inst_id' IMMEDIATE; 

Answer 5

 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.

Answer 6

inactive session the day before the murder

 begin for i in (select * from v$session where status='INACTIVE' and (sysdate-PREV_EXEC_START)>1) LOOP EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]' ||i.serial#||q'[']'||' IMMEDIATE'); END LOOP; end;