Re: ORA-01452 when creating a materialized view
- From: Maureen English <maureen.english@xxxxxxxxxx>
- To: l.flatz@xxxxxxxxxx
- Date: Mon, 15 Dec 2014 07:42:27 -0900
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
>
>
>
>
Other related posts:
