RE: Problem in dropping Materialized View

[babette.turnerunderwood]

Title: Message

Re-create the materialized view and then try to drop it. Because you tried to rename it to the view name, I would say try it both as the view name and as the original name.   I know it sounds silly but we had this problem once and creating it , will create whatever internal structures were needed to do the delete the MV. Re-creating the MV, put everything back to consistent state and we could drop cleanly   Babette    -----Original Message----- From: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Sent: 2003-10-17 2:54 AM To: Multiple recipients of list ORACLE-L Subject: Problem in dropping Materialized View Hi Gurus   Oracle 8.1.7 on HP Unix   I have some problems in Materialized view   Here are the sequence of steps Created a normal view CT_PRODUCTID_VW Created a materialized view CT_PRODUCID_MVW Dropped view CT_PRODUCTID_VW Rename CT_PRODUCTID_MVW to CT_PRODUCTID_VW   Now I cannot drop the materialized view CT_PRODUCTID_VW   SQL> select owner,object_type from all_objects where object_name='CT_PRODUCTID_VW';   OWNER                          OBJECT_TYPE ------------------------------ ------------------ COMMADM                        TABLE   SQL> ANALYZE TABLE CT_PRODUCTID_VW VALIDATE STRUCTURE CASCADE;   Table analyzed.   SQL>  DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW;  DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW * ERROR at line 1: ORA-12003: snapshot "COMMADM"."CT_PRODUCTID_VW" does not exist   SQL> drop snapshot COMMADM.CT_PRODUCTID_VW; drop snapshot COMMADM.CT_PRODUCTID_VW * ERROR at line 1: ORA-12003: snapshot "COMMADM"."CT_PRODUCTID_VW" does not exist   SQL> DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW; DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW * ERROR at line 1: ORA-12002: there is no snapshot log on table "COMMADM"."CT_PRODUCTID_VW"   SQL> drop table ct_productid_vw; drop table ct_productid_vw            * ERROR at line 1: ORA-12083: must use DROP MATERIALIZED VIEW to drop "COMMADM"."CT_PRODUCTID_VW"   The definition of the materialized view is as follows: CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT     msi.segment1                productid,               msi.description             description,     msi.inventory_item_id       inventory_item_id,     mc.segment1                 product_family,     mc.segment2                 product_type FROM [EMAIL PROTECTED]      mcs,      [EMAIL PROTECTED]         mc,      [EMAIL PROTECTED]    mic,      [EMAIL PROTECTED]       msi where 1=1 and   mc.structure_id       =  50112 and   mc.segment3          != 'SPARE' and   mc.global_name        = 'US' and   mc.enabled_flag       = 'Y' and   mcs.global_name       = mc.global_name and   mcs.category_set_name = 'PROD GROUP' and   mic.category_set_id   = mcs.category_set_id and   mic.category_id       = mc.category_id and   mic.global_name       = mc.global_name and   mic.organization_id   = 1 and   mic.inventory_item_id = msi.inventory_item_id and   msi.organization_id   = mic.organization_id and   msi.global_name       = mc.global_name AND   msi.auto_created_config_flag = 'N' AND   msi.item_type         IN ('ATO MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST>$0','PTO MODEL','SPARE') and   msi.inventory_item_status_code IN ('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')   Any help to drop this object is highly appreciated.           With Warm Regards Siddharth Haldankar Cisco Systems Inc. ODC Zensar Technologies Ltd. #  : 4128374 [EMAIL PROTECTED] [EMAIL PROTECTED]