-
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 .......
Santosh Jadhav
8i OCP DBA
-
Maybe the question is stupid but ...
Are you the owner of this objects?
Angel
-
Yes man ofcourse i'm the owner.
i've tried it from sys also. but the same error
Santosh Jadhav
8i OCP DBA
-
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:
Code:
create snapshot VIRU on prebuilt table as select * from XYZ;
Then drop it.
In 9i, it is impossible to rename MVs.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
Santosh Jadhav
8i OCP DBA
-
Julian no answers ?????
you have solved my problem but any explaination pl.
Santosh Jadhav
8i OCP DBA
-
Re: Julian no answers ?????
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 :-)
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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 .
Santosh Jadhav
8i OCP DBA
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|