Re: Re: ORA-01452 when creating a materialized view
From: <l.flatz_at_bluewin.ch>
Date: Mon, 15 Dec 2014 17:12:01 +0000
Message-ID: <22495912.45669.1418663521217.JavaMail.webmail_at_bluewin.ch>
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
Date: Mon, 15 Dec 2014 17:12:01 +0000
Message-ID: <22495912.45669.1418663521217.JavaMail.webmail_at_bluewin.ch>
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-lReceived on Mon Dec 15 2014 - 18:12:01 CET