How to drop users by mask in the Oracle RDBMS

Question

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

drop user where name like 'WIN%'

Answer 1

The DROP USER statement doesn't support a WHERE clause, much less LIKE and wildcarding.

You need to fetch a list of users from DBA_USERS that match, and iterate over that 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;