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

1. Created a normal view CT_PRODUCTID_VW
2. Created a materialized view CT_PRODUCID_MVW
3. Dropped view CT_PRODUCTID_VW
4. 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 MTL_CATEGORY_SETS_at_CME_ODSPROD      mcs,

     MTL_CATEGORIES_at_CME_ODSPROD         mc,

     MTL_ITEM_CATEGORIES_at_CME_ODSPROD    mic,

     MTL_SYSTEM_ITEMS_at_CME_ODSPROD       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

shaldank_at_cisco.com

s.haldankar_at_zensar.com  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Siddharth Haldankar
  INET: shaldank_at_cisco.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).