How to drop users by mask in the Oracle RDBMS

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

drop user where name like 'WIN%'

---

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;

---

In case cascade delete

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;
/

---

I had an error with the solution above without the BEGIN .. EXCEPTION .. END syntax. 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;
/

For a cascading deletion add || ' CASCADE' after i.username.