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: August 06, 2024 - 12:30 am UTC

Version: 19.16

Viewed 10K+ times! This question is

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:
https://livesql.oracle.com/apex/livesql/s/ot2y9dlu9sjf4lshqlhk4ei0q

and Connor said...

Looks like a bug

I've logged Bug 35133294 to track it.

Rating

  (2 ratings)

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

Same error in 19.23

ezequie, August 01, 2024 - 5:13 pm UTC

Was this ever fixed? Im getting the same error on 19c 23 with latest CPUs applied:

Cause: FDPSTP failed due to ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 216
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line

I cant see thosr bugs published in MOS
Connor McDonald
August 06, 2024 - 12:30 am UTC

Same in 21c and in 23ai

I'll give the bug a budge, but I suspect this one is a low priority fix.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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