Problem in dropping Materialized View

Siddharth Haldankar Thu, 16 Oct 2003 22:59:24 -0700

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)

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]

Problem in dropping Materialized View

Reply via email to