Re: ORA-01452 when creating a materialized view

  • From: "l.flatz@xxxxxxxxxx" <l.flatz@xxxxxxxxxx>
  • To: maureen.english@xxxxxxxxxx
  • Date: Mon, 15 Dec 2014 12:01:44 +0000 (GMT+00:00)

Hi Maureen,
You write you checked the master tables. If the MVIEW has joins it is still 
possible that we find duplicate keys in the MVIEW even though the master tables 
are ok,
An index create points to the MVIEW rather than to the master tables. Thus a 
unique index on the MVIEW should create your issues. 
How about creating your MVIEW as prebuild table and check for anything fishy?
You can try as well to build the MVIEW on top of the prebuild table and see it 
that gives you a work around.
Thanks
Lothar
----Ursprüngliche Nachricht----
Von : maureen.english@xxxxxxxxxx
Datum : 15/12/2014 - 11:17 (GMT)
An : oracle-l@xxxxxxxxxxxxx
Betreff : ORA-01452 when creating a materialized view
Hi,
I have what seems to me to be a very strange problem.
We just migrated our reporting instance from 10.2.0.4 to 11.2.0.2 (yes, I know 
we need to 
upgrade, but hardware issues are preventing that at the moment).  After the 
migration, most
things seem to be working, all the materialized views got created without 
errors as far as I 
can tell.  
For all of the materialized views that are done as fast refreshes, I need to do 
a complete refresh
before we can do the fast refreshes again because we use the refresh with rowid 
method.
I ran into a problem with 2 materialized views where I got the following error. 
 
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found when recreating a 
materialized view 
This happens on the create statement, after I dropped the materialized view.  
We checked the 
tables in the master database and they don't have any problems - my coworker 
ran analyze on 
them.
I do have a ticket in with Oracle, but thought that maybe someone has seen this 
before and 
can offer a suggestion.
I'm concerned that since this is a production reporting instance, I'll have 
users who will be 
unable to get their data tomorrow morning....
- Maureen
  

Other related posts: