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: Shakespeare on 16 Apr 2010 15:39 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 > > Just got this from metalink: sometimes when the MV is dropped, the MV-table with the same name still exists (a MV has a table with the same name). It's a bug. Metalink note 565532.1 Shakespeare
From: frank on 18 Apr 2010 05:20 Shakespeare wrote: > > Just got this from metalink: sometimes when the MV is dropped, the > MV-table with the same name still exists (a MV has a table with the same > name). It's a bug. > Metalink note 565532.1 > > Shakespeare Wouldn't that be while using "create MV on prebuilt table as select ..."? Not if you just did "create MV as select". OP: would there perhaps be a state in time, where a prebuilt table existed? Or a synonym with the same name? Regards, frank van bortel
From: Shakespeare on 18 Apr 2010 06:03 Op 18-4-2010 11:20, frank schreef: > Shakespeare wrote: > >> >> Just got this from metalink: sometimes when the MV is dropped, the >> MV-table with the same name still exists (a MV has a table with the >> same name). It's a bug. >> Metalink note 565532.1 >> >> Shakespeare > > Wouldn't that be while using "create MV on prebuilt table as select ..."? > Not if you just did "create MV as select". > > OP: would there perhaps be a state in time, where a prebuilt table > existed? Or a synonym with the same name? > > Regards, > frank van bortel The note does not mention that. Some references do, though. It states: It is possible that an earlier drop of Mview did not complete successfully. It could be verified by checking dba_objects. Shakespeare.
From: Norbert Winkler on 19 Apr 2010 11:46
Am Thu, 15 Apr 2010 10:33:17 -0700 (PDT) schrieb The Magnet: > Suggestions? http://www.dba-oracle.com/art_9i_mv.htm has some suggestions under "Refreshing materialized views" I've tested succesfully: --- EXECUTE DBMS_MVIEW.REFRESH('a_mat_view_name'); --- declare number_of_failures integer; begin DBMS_MVIEW.REFRESH_DEPENDENT(number_of_failures,'master_tab1,master_tab2,master_tab3'); end; / ------------- this fails: DBMS_MVIEW.REFRESH_ALL_MVIEWS(number_of_failures); ------------- and this (refresh on commit!!!!!) fails too for my views: CREATE MATERIALIZED VIEW empdep ON PREBUILT TABLE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT empno, ename, dname, loc, e.rowid emp_rowid, d.rowid dep_rowid FROM emp e, dept d WHERE e.deptno = d.deptno; -- Norbert Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production |