ORA-23401: materialized view OWNER.MVIEW does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see the error

ORA-23401: materialized view OWNER.MVIEW does not exist

when you execute a procedure like:

begin
dbms_mview.refresh('OWNER.MVIEW','C');
end;

or

begin
dbms_mview.refresh('MVIEW','C');
end;

Clearly there can be a variety of reasons, with the most trivial one being that you mistyped the materialized view name. Please note that if the object was created with a case sensitive name, you must take care of writing the name using the proper casing:

Suppose that you create a materialized view with a name like this:

create materialized view "Ugly_Name"
refresh complete on demand
with rowid
as
select * from dual;

If you execute:

begin
dbms_mview.refresh('Ugly_Name','C');
end;

ORA-23401: materialized view "USER"."UGLY_NAME" does not exist

bacause you need to surround the name with double quotes:

begin
dbms_mview.refresh('"Ugly_Name"','C');
end;

Note that if you are trying to do this on a materialized view residing in a different schema, you may get ORA-23401 if you omit entirely or use an improper letter casing for the schema name:

begin
dbms_mview.refresh('OTHER_USER."Ugly_Name"','C'); -- it should be "Other_User"
end;

or even if the user name is case sensitive and you mistype it...

begin
dbms_mview.refresh('"Other_user"."Ugly_Name"','C'); -- it should be "Other_User"
end;

But, this is not enough, in order to succeed you must hold the ALTER ANY MATERIALIZED VIEW system privilege, otherwise you'll get ORA-01031 eventually.

See message translations for ORA-23401 and search additional resources