Re: Re: ORA-01452 when creating a materialized view

From: Maureen English <maureen.english_at_alaska.edu>
Date: Wed, 17 Dec 2014 06:17:53 -0900
Message-ID: <CAAzCTjH6aOGhxkfzJadCs=0-FoOfBBh79ZxiMTRrotKiCUFkHA_at_mail.gmail.com>



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_at_bluewin.ch <l.flatz_at_bluewin.ch> 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_at_alaska.edu
> Datum : 15/12/2014 - 17:42 (GMT)
> An : l.flatz_at_bluewin.ch
> Cc : oracle-l_at_freelists.org
> 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_at_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_at_bluewin.ch <l.flatz_at_bluewin.ch>
> 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_at_alaska.edu
>> Datum : 15/12/2014 - 11:17 (GMT)
>> An : oracle-l_at_freelists.org
>> 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
>>
>>
>>
>>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 17 2014 - 16:17:53 CET

Original text of this message