CODEWITHSUNDEEP
sqlvieworacle11g
Agricola
Agricola

Reputation: 602

Drop All Views in an Oracle Schema

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

Upvotes: 4

Views: 14718

Answers (3)

mayeulk
mayeulk

Reputation: 148

The following query will generate the SQL statements that you need to run (you'd copy-paste the result, double-check then run the statements):

select 'drop view "'||view_name||'";' as statements from user_views;

This is a modified version of the other answers based on select. The difference here are the double quotes. Without the double quotes, dropping views whose name starts with a number, an underscore... will raise an error.

Incorrect SQL:

drop view 123_MY_VIEW;
drop view _MY_OTHER_VIEW;

Correct SQL:

drop view "123_MY_VIEW";
drop view "_MY_OTHER_VIEW";

Upvotes: 0

Agricola
Agricola

Reputation: 602

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.

Upvotes: 10

Brian DeMilia
Brian DeMilia

Reputation: 13248

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.

Upvotes: 3

Related Questions