RE: Problem in dropping Materialized View

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-----
Sent: 2003-10-17 2:54 AM
To: Multiple recipients of list ORACLE-L

        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: <babette.turnerunderwood_at_hrdc-drhc.gc.ca
  INET: babette.turnerunderwood_at_hrdc-drhc.gc.ca

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).