Lothar, rest of list; The problem turned out to be a data corruption issue in the source database. There are 2 rows in one of the source tables with the same rowid value. I'm pretty sure I know how that happened and that we can fix it quickly. Thanks. - Maureen On Mon, Dec 15, 2014 at 8:12 AM, l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx> wrote: > Hi Maureen, > > the sql statement that is the base of the MVIEW can include joins. > (Unfortunately that statement is not given.) > That is a possible explaination that the might be duplicates in the MVIEW > but not in the master tables. > Thus the non existence of duplicates in the master tables are not yet a > proof that there are no duplicates in the resulting MVIEW. > Before you continue it is important to verify that there are really no > duplicates. > Given that they MVIEW was functioning so far it is hard to explain that > there might be duplicates, but it is not impossible.E.g. very recent data > might the reason. > So far it is just on assumption that the error message is incorrect. > (Unless you did some test that you did not tell us about.) > Let us check the assumption. > > Thanks > > Lothar > > ----Ursprüngliche Nachricht---- > Von : maureen.english@xxxxxxxxxx > Datum : 15/12/2014 - 17:42 (GMT) > An : l.flatz@xxxxxxxxxx > Cc : oracle-l@xxxxxxxxxxxxx > Betreff : Re: ORA-01452 when creating a materialized view > > Lothar; > > How could the mview have joins when I'm just recreating it? > > I completely dropped the materialized view in the reporting instance, > dropped > and recreated the mlog table in the source database and even dropped a > public > synonym that was created in the source database and pointing to the mlog > table. > I'm not sure why that was there, though. It looks like the error happens > when it's > trying to create the I_SNAP$_SORLFOS index that gets created automatically > when the materialized view is created. > > create materialized view saturn.sorlcur > TABLESPACE STUDENT > using index tablespace SATINDEX > refresh with rowid > as select * from saturn.sorlcur@MYPRODDB; > * > ERROR at line 5: > ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found > > - Maureen > > > > On Mon, Dec 15, 2014 at 3:01 AM, l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx> > wrote: > >> 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 >> >> >> >> > > >