How to remove masked users in Oracle RDBMS

Question

I want to delete all users who have "WIN" at the beginning of their name (for example, "WIN $ DOWS"). Is it possible to write something like follownig?

drop user where name like 'WIN%'

Answer 1

The DROP USER statement does not support the WHERE clause, much less the LIKE and wildcard.

You need to get a list of users from DBA_USERS that matches and iterates over this list:

--Bye Users!
FOR i IN (SELECT t.username
            FROM DBA_USERS t
           WHERE t.username LIKE 'WIN%') LOOP
  EXECUTE IMMEDIATE 'drop user '|| i.username ||'';
END LOOP;

Answer 2

I have been a mistake with the solution above without syntax BEGIN.. EXCEPTION.. END. This works for me:

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username;
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

|| ' CASCADE' i.username.

Answer 3

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'WIN%') 
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username || ' CASCADE';
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/