Skip to Main Content
  • Questions
  • Oracle 19c trying to refresh a non-existent materialized view results in error ORA-01031: insufficient privileges instead of ORA-23401: materialized view does not exist

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Petko.

Asked: February 28, 2023 - 2:10 pm UTC

Last updated: February 26, 2024 - 5:57 am UTC

Version: 19.16

Viewed 1000+ times

You Asked

Please advise on why in Oracle 19c (19.16) trying to refresh a non-existent materialized view results in error ORA-01031: insufficient privileges instead of ORA-23401: materialized view does not exist.

begin 
  dbms_mview.refresh('scott.nonExistentViewHere'); 
 
end; 


ORA-20000: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020 ORA-06512: at "SYS.DBMS_IREFRESH", line 186 ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 189 ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2852 ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263 ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16 ORA-06512: at line 5 ORA-06512: at "SYS.DBMS_SQL", line 1721

In Oracle 12.2 this same code to refresh a non-existent view shows the expected ORA-23401: materialized view does not exist

with LiveSQL Test Case:

and Connor said...

Looks like a bug

I've logged Bug 35133294 to track it.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-01031 When refreshing MVW

Jason, February 21, 2024 - 5:56 pm UTC

Hi

Was this big ever addressed? (Cant see it in MOS).

We are seeing the same issue with 19.21.

Thanks
Jason
Connor McDonald
February 26, 2024 - 5:57 am UTC

No fix yet.

The bug was previously detected so the original bug # is now 35075578

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.