| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem in dropping Materialized View
Hitchman
Thanks a ton, Yes it is a bug.
With Warm Regards
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies Ltd.
# : 4128374
shaldank_at_cisco.com
s.haldankar_at_zensar.com
-----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 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
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). -- 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).Received on Fri Oct 17 2003 - 05:04:25 CDT
 |
 |