Script to kill all session of a user in Oracle

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.

Script

BEGIN
FOR r IN (select sid,serial# from v$session where username = 'HR')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid|| ',' || r.serial# || ''' immediate';
END LOOP;
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.