Dropping Oracle connections using a query

Question

Hi.

I wanted to drop/kill connections made to specific schema of a database. Could you please sugest a prudent way to do this?

Cheers

Answer 1

Hi Priyank,

In order to prevent a user from connecting you could lock the account:

ALTER USER usr ACCOUNT LOCK;

If you want to disconnect all sessions of a user, you could use the method 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;