Removing Oracle Connections Using a Query

Question

I wanted to remove / kill connections made for a particular database schema. Could you suggest a reasonable way to do this?

Greetings

Answer 1

To prevent a user from connecting, you can lock the account:

ALTER USER usr ACCOUNT LOCK;

If you want to disconnect all user sessions, you can use the ones described in another SO :

BEGIN
   FOR x IN (SELECT Sid, Serial# FROM v$session WHERE username = 'USR') LOOP
      EXECUTE IMMEDIATE 'Alter System Kill Session ''' || x.Sid || ',' 
                        || x.Serial# || ''' IMMEDIATE';
   END LOOP;
END;