Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2474
  • Last Modified:

Error in creating a materialized view (ora-00942)

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
diteps06
Asked:
diteps06
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Try an explicit grant not through a view.

Log into src and execute:
grant select on cars to tr;
0
 
diteps06Author Commented:
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
 
hinpongCommented:
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
 
slightwv (䄆 Netminder) Commented:
hinpong,

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

Please read previous posts before responding.
0
 
slightwv (䄆 Netminder) Commented:
diteps06,

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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now