Script to kill all session of a user in Oracle
Place the name of username you want to kill the session. In example i want to kill all the session of HR schema.
Note: If you are running from HR user with on current session then only your session is not disconnected. Script will give error “ORA-00027: cannot kill current session”. Rest all other session is disconnected.
FOR r IN (select sid,serial# from v$session where username = 'HR')
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid|| ',' || r.serial# || ''' immediate';