Materialized Views
in [Oracle Help]
|
Prev: Get colorful htm outputs with sql-oracle batch scripts and tips - www.sqlhtm.com/sqlhtm
Next: Toubles Storing ASCII character greater than 127
From: The Magnet on 15 Apr 2010 13:33 We're about at the end of our rope. We have a script that runs at night and re-created some materialized view. The SQL simple drops the view and re-creates it. However, each night a different view fails to be created. The view is dropped, but when attempting we'll get a ORA-00955: FROM analyst a, broker b, master_table m, ( * ERROR at line 14: ORA-00955: name is already used by an existing object How can you get that error on a SELECT?? And, it is different every time. I thought is was timing, but that is not it. Suggestions?
From: Michel Cadot on 15 Apr 2010 13:36 "The Magnet" <art(a)unsu.com> a �crit dans le message de news: 059a76ea-1b9d-486d-bd56-ae3857f9b893(a)w17g2000yqj.googlegroups.com... | | We're about at the end of our rope. We have a script that runs at | night and re-created some materialized view. The SQL simple drops the | view and re-creates it. | | However, each night a different view fails to be created. The view is | dropped, but when attempting we'll get a ORA-00955: | | FROM analyst a, broker b, master_table m, ( | * | ERROR at line 14: | ORA-00955: name is already used by an existing object | | How can you get that error on a SELECT?? And, it is different every | time. I thought is was timing, but that is not it. | | Suggestions? Because the optimizer generates intermediate memory views and unfortunatly (and bugly) it generates trwice the same name. Regards Michel
From: The Magnet on 15 Apr 2010 13:42 On Apr 15, 12:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "The Magnet" <a...(a)unsu.com> a écrit dans le message de news: 059a76ea-1b9d-486d-bd56-ae3857f9b...(a)w17g2000yqj.googlegroups.com... > | > | We're about at the end of our rope. We have a script that runs at > | night and re-created some materialized view. The SQL simple drops the > | view and re-creates it. > | > | However, each night a different view fails to be created. The view is > | dropped, but when attempting we'll get a ORA-00955: > | > | FROM analyst a, broker b, master_table m, ( > | * > | ERROR at line 14: > | ORA-00955: name is already used by an existing object > | > | How can you get that error on a SELECT?? And, it is different every > | time. I thought is was timing, but that is not it. > | > | Suggestions? > > Because the optimizer generates intermediate memory views and > unfortunatly (and bugly) it generates trwice the same name. > > Regards > Michel If this is true, the I have 2 questions: 1) Why does it not happen to every view 2) How to get around it? We have 7 views which we create. Thanks.
From: Michel Cadot on 15 Apr 2010 14:16 "The Magnet" <art(a)unsu.com> a �crit dans le message de news: 57abad0c-82ca-404c-b6bb-e7a8a80a6cfa(a)r18g2000yqd.googlegroups.com... On Apr 15, 12:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "The Magnet" <a...(a)unsu.com> a �crit dans le message de news: 059a76ea-1b9d-486d-bd56-ae3857f9b...(a)w17g2000yqj.googlegroups.com... > | > | We're about at the end of our rope. We have a script that runs at > | night and re-created some materialized view. The SQL simple drops the > | view and re-creates it. > | > | However, each night a different view fails to be created. The view is > | dropped, but when attempting we'll get a ORA-00955: > | > | FROM analyst a, broker b, master_table m, ( > | * > | ERROR at line 14: > | ORA-00955: name is already used by an existing object > | > | How can you get that error on a SELECT?? And, it is different every > | time. I thought is was timing, but that is not it. > | > | Suggestions? > > Because the optimizer generates intermediate memory views and > unfortunatly (and bugly) it generates trwice the same name. > > Regards > Michel If this is true, the I have 2 questions: 1) Why does it not happen to every view 2) How to get around it? We have 7 views which we create. Thanks. ----------------------------- 1) Because it is a bug and so happens only in certain circonstances 2) Try to change the way you write it It could happen on any complex query not only in mview. Regards Michel
From: Shakespeare on 16 Apr 2010 15:12
Op 15-4-2010 20:16, Michel Cadot schreef: > "The Magnet"<art(a)unsu.com> a �crit dans le message de news: 57abad0c-82ca-404c-b6bb-e7a8a80a6cfa(a)r18g2000yqd.googlegroups.com... > On Apr 15, 12:36 pm, "Michel Cadot"<micadot{at}altern{dot}org> wrote: >> "The Magnet"<a...(a)unsu.com> a �crit dans le message de news: 059a76ea-1b9d-486d-bd56-ae3857f9b...(a)w17g2000yqj.googlegroups.com... >> | >> | We're about at the end of our rope. We have a script that runs at >> | night and re-created some materialized view. The SQL simple drops the >> | view and re-creates it. >> | >> | However, each night a different view fails to be created. The view is >> | dropped, but when attempting we'll get a ORA-00955: >> | >> | FROM analyst a, broker b, master_table m, ( >> | * >> | ERROR at line 14: >> | ORA-00955: name is already used by an existing object >> | >> | How can you get that error on a SELECT?? And, it is different every >> | time. I thought is was timing, but that is not it. >> | >> | Suggestions? >> >> Because the optimizer generates intermediate memory views and >> unfortunatly (and bugly) it generates trwice the same name. >> >> Regards >> Michel > > If this is true, the I have 2 questions: > > 1) Why does it not happen to every view > 2) How to get around it? > > We have 7 views which we create. > > Thanks. > > ----------------------------- > > 1) Because it is a bug and so happens only in certain circonstances > 2) Try to change the way you write it > > It could happen on any complex query not only in mview. > > Regards > Michel > > Sometimes 'purge recyclebin' helps. Shakespeare |