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_NAME STATUS ------------------------------ ------------------------------ -------- DEBTOR_CLAIM_COMPONENTS FK_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). -- 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).
