Pages

Monday, March 9, 2015

PL/SQL: Dropping a connected user

Here's a quick script I wrote to disconnect and then drop a connected user.

Just replace with the user you want to remove.

BEGIN
  FOR sql_command IN (select 'alter system kill session ''' || sid || ',' || serial# || '''' AS command FROM v$session WHERE username = '') LOOP
    EXECUTE IMMEDIATE
      sql_command.command;
  END LOOP;
  
  EXECUTE IMMEDIATE
    'DROP USER  CASCADE';
  COMMIT;
END;

You could make a parameter fairly easily, but this patch of code met my immediate needs (there was an web front end that was continually trying to connect but I didn't want to shut it down, just drop and re-create the user).