Drop All Views in an Oracle Schema

Question

I would like a single query to drop all of the views in my oracle schema. I know this isn't correct syntax (or I wouldn't be asking), but the idea that I am going for is like the following:

DROP VIEW (SELECT view_name FROM user_views);

Solution 1

I broke down and used a PL/SQL block like the following:

    begin
      for i in (select view_name from user_views) loop
        execute immediate 'drop view ' || i.view_name;
      end loop;
    end;

If anybody knows a single query solution, I would still be curious.

Solution 2

You could use this query to generate the statements that you need to run (and then run the statements):

select 'drop view '||view_name||';' as statements
from all_views
where owner = 'YOUR_SCHEMA_NAME'

Be careful that you don't inadvertently remove any views in your schema that might have been created by someone other than you and which you might need, if such is possible in your particular circumstances. For example, if you use Oracle Data Miner, it stores a number of objects in your schema as you create workflows and such.