Hitchman
Thanks a ton, Yes it is a bug.
With Warm Regards
_____
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies Ltd.
# : 4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
-----Original Message-----
Hitchman, Peter
Sent: Friday, October 17, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
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 [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]
__________________________________________________________________
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: [EMAIL PROTECTED]
__________________________________________________________________
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hitchman, Peter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Siddharth Haldankar
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
