Oracle PL/SQL: Removing an Existing Materialized View

This blog post provides a piece of SQL that you can easily customize to check-for and remove a materialized view if it is present.


The SQL is;

declare
  v_MVName varchar2(30) := 'XXX';
begin
  for v_Row in (select 'x' from all_mviews am where am.owner = user and am.mview_name = v_MVName) loop
  execute immediate
    'drop materialized view ' || v_MVName;
  end loop;
end;

In order to target a specific view just replace XXX in the second line with the name of your view.