RE: dropping materialized view

From: Jeroen van Sluisdam <jeroen.van.sluisdam_at_vrijuit.nl>
Date: Tue, 09 Dec 2003 11:39:41 -0800
Message-ID: <F001.005D950D.20031209113941@fatcity.com>

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:mshakir08816_at_yahoo.com] 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 <jeroen.van.sluisdam_at_vrijuit.nl> 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: mshakir08816_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

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 Tue Dec 09 2003 - 13:39:41 CST