dropping materialized view
Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen
Re: dropping materialized view
Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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).
RE: dropping materialized view
select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: Jeroen van Sluisdam 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).
RE: dropping materialized view
Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: Jeroen van Sluisdam 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: Igor Neyman 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).
RE: dropping materialized view
Oops I feel very stupid, thanks a lot Igor this did the tric. Regards Jeroen -Oorspronkelijk bericht- Van: Igor Neyman [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 21:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: dropping materialized view Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: Jeroen van Sluisdam 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: Igor Neyman 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
RE: dropping materialized view
Don't (feel stupid) :) It happens to all of us... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Oops I feel very stupid, thanks a lot Igor this did the tric. Regards Jeroen -Oorspronkelijk bericht- Van: Igor Neyman [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 21:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: dropping materialized view Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: Jeroen van Sluisdam 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: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Problem in dropping Materialized View
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
-- --
COMMADMTABLE
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.segment1productid,
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).
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
[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
-- --
COMMADMTABLE
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.segment1productid,
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:
RE: Problem in dropping Materialized View
Title: Message
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-From:
Siddharth Haldankar [mailto:[EMAIL PROTECTED] Sent: 2003-10-17 2:54
AMTo: Multiple recipients of list ORACLE-LSubject: Problem
in dropping Materialized View
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]
Problem in dropping Materialized View
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]
