DROP MATERIALIZED VIEW

[sjadhavdba]

I'm getting an error while dropping the materialized view -

drop materialized view viru
*
ERROR at line 1:
ORA-12003: snapshot "INFINITY"."VIRU" does not exist

i've also tried drop snapshot but the same error

and if try to do
DROP TABLE VIRU
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "INFINITY"."VIRU"

any one .......

[aarroyob]

Maybe the question is stupid but ...

Are you the owner of this objects?

Angel

[sjadhavdba]

Yes man ofcourse i'm the owner.

i've tried it from sys also. but the same error

[julian]

I have the feeling someone has been renaming materialized views :-)

Ask around from which table the renamed MV was recreated from. Say the table is called XYZ.

Then run:

create snapshot VIRU on prebuilt table as select * from XYZ;

Then drop it.

In 9i, it is impossible to rename MVs.

[sjadhavdba]

Oh Julian,

it really worked.. thanx for the solution. but can u explain me that if any person renamed it then what the problem occurs??

thanx once again

[sjadhavdba]

you have solved my problem but any explaination pl.

[julian]

Originally posted by sjadhavdba
you have solved my problem but any explaination pl.

Someone has renamed the MV from OLD_NAME to VIRU. Oracle still know is as OLD_NAME. You run drop MV VIRU, Oracle checks for such a MV, doesn't find one, reports to you MV VIRU not found. That's it :-)

[sjadhavdba]

Thanks julian,
In this case oracle shud upadate dictionary too with new name is it descripancy in oracle ???

And what if this solution does not work for similar situations how to delete user containing that MV forcefully.

Becaz " drop user xyz cascade ; " does not work .

[julian]

Originally posted by sjadhavdba
Thanks julian,
In this case oracle shud upadate dictionary too with new name is it descripancy in oracle ???

Renaming MVs is a non-documented feature of Oracle. You should not use it.

And what if this solution does not work for similar situations how to delete user containing that MV forcefully.

Becaz " drop user xyz cascade ; " does not work .

In case the table on which the MV is based does not exist, you still have one option. Create a table with a same structure as the dropped table. Then you can run the command that I gave you in my first reply.