Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error in creating a materialized view (ora-00942)

Posted on 2013-10-23
6
Medium Priority
?
2,415 Views
Last Modified: 2013-10-25
I will like to create a materialized view on a schema tr which has select grant on a table cars on a different schema src
I used the following statement
CREATE MATERIALIZED VIEW tr.cars_mv
TABLESPACE xxxx
LOGGING
       REFRESH FAST
  WITH PRIMARY KEY
       AS
SELECT
Name,
Type
FROM src.cars
;

I am having the following  error:
ora-00942: table or view does not exist

I have done the following checks
•      the spelling of the table or view name.
•      that a view is not specified where a table is required.
•      that an existing table or view name exists.

In fact if I run the query under tr it functions that is without the create materialized view statement

What could be the source of the error. Could it be related to the characteristics of the materialized view (  REFRESH FAST ) to be created
0
Comment
Question by:diteps06
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1920 total points
ID: 39593784
Try an explicit grant not through a view.

Log into src and execute:
grant select on cars to tr;
0
 
LVL 1

Author Comment

by:diteps06
ID: 39594221
I forgot to mention a detail the table cars have a materialized view log table created on it.
the schema tr doesn't yet have a grant select on it. Maybe this is cause of the error. I am still to verify it
0
 
LVL 1

Assisted Solution

by:hinpong
hinpong earned 80 total points
ID: 39599217
They are under two different schema, tr and src? If so, you need to grant select privilges

connect src
grant select on cars to tr;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39599265
hinpong,

How is your post any different than what I have already posted?

Please read previous posts before responding.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39599381
diteps06,

Mind if I ask why the "B" penalty grade?  What additional assistance did you need before providing an "A" grade.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question