Re: How to find the index for a constraint

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Fri, 15 Jun 2001 00:30:46 +0200
Message-ID: <3B293B16.B2985CC1@0800-einwahl.de>

Hello Lothar,

for a constraint in your own schema try

select	--+ rule
	o.owner as index_owner
	, o.object_name as index_name
	, n.name as constraint_name
from	sys.cdef$ c
	, dba_objects o
	, sys.con$ n

where 1 = 1

and c.enabled = o.object_id
and c.con# = n.con#
and n.owner# = uid

/

This was posted several months ago by Thomas Kyte.

Martin

Lothar Armbrüster wrote:
>
> Hello out there,
>
> I'm looking for a method to find the index name for a, say, primary key
> constraint. Normally the index is named like the constraint, but today I
> came upon a table where the constraint seemed to use another index.
> I wanted to drop an index but Oracle said this index could not be
> dropped:
>
> ORA-02429 cannot drop index used for enforcement of unique/primary key
>
> But this index had the PK column and one additional column. The index
> named like the constraint also existed.
> After I did "alter table ... drop primary key" I could drop the index.
> I wasn't droped with the constraint.
>
> I look for a data dictionary view which shows me the connection between
> index and constraint, but I cannot find one which contains INDEX_NAME
> and CONSTRAINT_NAME.
>
> The system ist Oracle 8.1.7 on NT4.
>
> Many thanks in advance,
> Lothar
>
> --
> Lothar Armbrüster | la_at_oktagramm.de
> Hauptstr. 26 | la_at_heptagramm.de
> D-65346 Eltville | lothar.armbruester_at_t-online.de
Received on Thu Jun 14 2001 - 17:30:46 CDT