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?

"The Magnet" <art (AT) unsu (DOT) com> a écrit dans le message de news: 059a76ea-1b9d-486d-bd56-ae3857f9b893...oglegroups.com...
Because the optimizer generates intermediate memory views and
unfortunatly (and bugly) it generates trwice the same name.

Regards
Michel

On Apr 15, 12:36*pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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.

"The Magnet" <art (AT) unsu (DOT) com> a écrit dans le message de news: 57abad0c-82ca-404c-b6bb-e7a8a80a6cfa...oglegroups.com...
On Apr 15, 12:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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

Op 15-4-2010 20:16, Michel Cadot schreef:
Sometimes 'purge recyclebin' helps.

Shakespeare

Op 15-4-2010 20:16, Michel Cadot schreef:
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

Shakespeare wrote:

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

Op 18-4-2010 11:20, frank schreef:
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.

Am Thu, 15 Apr 2010 10:33:17 -0700 (PDT) schrieb The Magnet:

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,'m aster_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