2 Replies Latest reply on Jul 18, 2006 5:58 AM by 8734

    Can't drop table/ materialized view

    229233
      Hello,

      I have a materialized view that cannot be dropped whether I try to drop it as a table or materialized view. Oracle says it drops the materialized view OK but when I try and create a table with the same name it says that the name is already in use!

      Below is an example script of the problem. I am running on Oracle 10g.

      DROP TABLE VIC_86173_18
      ORA-12083: must use DROP MATERIALIZED VIEW to drop "QA"."VIC_86173_18"

      DROP MATERIALIZED VIEW VIC_86173_18
      Snapshot dropped

      CREATE TABLE VIC_86173_18
      (GEOMETRY MDSYS.SDO_GEOMETRY,lga_polygon_pid VARCHAR2(15),
      date_created DATE,date_retired DATE,lga_pid VARCHAR2(15))
      ORA-00955: name is already used by an existing object

      Thanks
      Bronwyn
        • 1. Re: Can't drop table/ materialized view
          229233
          More information:

          I can run the 'DROP MATERIALIZED VIEW' command over and over without getting an error. eg:

          DROP MATERIALIZED VIEW VIC_86173_18
          Snapshot dropped

          DROP MATERIALIZED VIEW VIC_86173_18
          Snapshot dropped

          DROP MATERIALIZED VIEW VIC_86173_18
          Snapshot dropped

          DROP MATERIALIZED VIEW VIC_86173_18
          Snapshot dropped

          DROP MATERIALIZED VIEW VIC_86173_18
          Snapshot dropped
          • 2. Re: Can't drop table/ materialized view
            8734
            Hi

            Try this please.

            1. Create a snapshot on the table which can not be dropped using the 'on
            prebuilt table' option:
            create materialized view test on prebuilt table as select ... ;
            2. Drop the snapshot
            3. Drop the table

            I found this on metalink. I hope it solves your issue.

            Regards
            Adnan