DROP MATERIALIZED VIEW
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: DROP MATERIALIZED VIEW

  1. #1
    Join Date
    Dec 2001
    Posts
    221
    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

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Maybe the question is stupid but ...

    Are you the owner of this objects?

    Angel

  3. #3
    Join Date
    Dec 2001
    Posts
    221
    Yes man ofcourse i'm the owner.

    i've tried it from sys also. but the same error
    Santosh Jadhav
    8i OCP DBA

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  5. #5
    Join Date
    Dec 2001
    Posts
    221
    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

  6. #6
    Join Date
    Dec 2001
    Posts
    221

    Julian no answers ?????

    you have solved my problem but any explaination pl.
    Santosh Jadhav
    8i OCP DBA

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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

  8. #8
    Join Date
    Dec 2001
    Posts
    221
    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

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
  •  


Click Here to Expand Forum to Full Width