Oracle-L: RE: Problem in dropping Materialized View

From: Hitchman, Peter <peter.hitchman_at_derwent.co.uk>
Date: Fri, 17 Oct 2003 01:34:26 -0800
Message-ID: <F001.005D36D9.20031017013426@fatcity.com>

Hi,
Did you rename the materialized view? If so you are probably hitting bug 1335477. Can you see the view name in user_mviews? I think you have to try and re-create the materialized view and then drop it.

Really suggest you contact Oracle, unless someone on the list has the definitive fix.

Regards

Pete

[END] -----Original Message-----
Sent: 17 October 2003 07:54
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
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 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

The information contained in this email is confidential and intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. Thomson Scientific will accept no responsibility or liability in respect to this email other than to the addressee. If you have received this communication in error, please notify us immediately via email: ITHelpdesk_at_derwent.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hitchman, Peter
  INET: peter.hitchman_at_derwent.co.uk

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

Received on Fri Oct 17 2003 - 04:34:26 CDT