Solved

how do I create multiple materialized views using the same table from database link connection

Posted on 2014-03-31
2
972 Views
Last Modified: 2014-04-09
I have a test virtual environment that i am trying to develop in the same way that production is currently configured. In production there are 5 materialized views that query data from another database. I am not the creator of the virtual environment. It is an inheritance. The person before me only created 4 of the materialized views in the virtual environment. I used the ddl from production to try to create the missing materialized view in the virtual environment. I kept getting the error message ORA-01031: insufficient privileges. I could query the table referenced in the materialized view that I was trying to create. I checked the privileges in production against the virtual environment and they were the same. After much searching i just started testing adding privileges. When I granted the schema in the virtual environment global query rewrite I got a new error. ORA-00955: name is already used by an existing object.

There is no other materialized view name in virtual environment that is already being used. I did notice that the tables that are referenced in the materialized views exist in the schema in the virtual environment. The table I am referencing in the materialized view is also used in 2 of the 5 materialized views.

4 of the 5 materialized views are being refreshed using force in production and the virtual environment. The one that I am trying to create is a complete refresh.

How do I successfully create the materialized view that is missing in the virtual environment?
0
Comment
Question by:sikyala
2 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 39967366
you may be creating it with the same name which already exists in the database.. try to recreate it with a different name and use a synonym to point it to the created object.
0
 

Author Closing Comment

by:sikyala
ID: 39989353
You were absolutely right. There was a table with the exact same name. I deleted it and I was able to create the materialized view.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 50
Oracle 12c patching 1 60
Oracle Pivot Question 8 44
Export table into csv file in oracle 10 41
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now