2 Replies Latest reply on Oct 30, 2015 8:40 PM by rp0428

    Dropping Materialized View Error

    Earl Lewis

      I tried to drop two materialized views (one that I wanted to drop and the other as a test to make sure the behavior was consistent) in SQL Developer today, using the GUI, not the SQL command window. When I did this, since SQL Developer had these MVs in among the tables, it tried to run a "DROP TABLE" statement rather than a "DROP MATERIALIZED VIEW" and an ugly error message came along with that and of course it failed. Is this a bug that needs to be addressed? Or perhaps it's been identified already and being addressed in a future release?

       

      Earl

        • 1. Re: Dropping Materialized View Error
          Jim Smith

          It sounds like you are dropping the underlying table, not the materialized view.   If you have a materialized view my_mv, there is also a table called my_mv.

           

          You need to drop the materialized view by from the Materialized View node in the object browser.

          • 2. Re: Dropping Materialized View Error

            When I did this, since SQL Developer had these MVs in among the tables, it tried to run a "DROP TABLE" statement rather than a "DROP MATERIALIZED VIEW" and an ugly error message came along with that and of course it failed.

            No - Sql Dev did NOT have MVs 'in among the tables' - the tables node ONLY has tables.

             

            See the 'CREATE MATERIALIZED VIEW' section of the Oracle docs for the explanation of the other responder's reply:

            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm

            When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data.

            Do this simple test:

             

            1. examine the list of tables for a table named 'ABC'

            2. examine the list of MVs for one named 'ABC'

            3. create an MV named 'ABC'

             

            If you reexamine both lists you will find an MV named 'ABC' in the materialized view node and a table named 'ABC' in the tables node.

            1 person found this helpful