Re: Can't drop materialized view

"Markus Boehmer" <markus.boehmer_at_schaefer-shop.de> wrote in message news:bn5c4p$45a$1_at_news.dtag.de...
> Anurag Varma wrote:
> >
> >
> > What is the output of the following:
> >
> > select * from all_mviews where mview_name = 'WARENKORBPOS';
> >
> > ?
> >
> > Anurag
> >
> >
>
>
> Well, that's strange...
> SQL> select * from all_mviews where mview_name = 'WARENKORBPOS';
>
> no rows selected
>
>
> Markus
>

Hi all.

Markus, did you upgrade your database from Oracle 8i to 9iR2?

The reason I ask this is that in Oracle 8i, bugs allowed you to drop the container table (sort of fixed in 8.1.7) or to rename the container table giving it a different name to it's materialized view. In Oracle 8.1.7 having a container table with a different name to it's materialized view poses an additional problem as the materialized view can be dropped but the container table cannot be dropped. The Oracle server tells you that you must drop the materialized view instead.

Note that this bug was fixed in Oracle 9i by disallowing the rename commands on the materialized view or it's container table:

Assuming you have dropped the materialized view then you need to re-create it using the ON PREBUILT TABLE option. You should then be able to drop the materialized view and finally drop the table as a separate step. If on the other hand, the materialized view exists but has a different name to the table then I would say you are screwed. You could try creating a materialized view using the ON PREBUILT TABLE option but I don't think it will let you.

Cheers Received on Wed Oct 22 2003 - 02:38:53 CDT