Re: How do you find a index which belongs to a primary key

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Thu, 28 Mar 2002 14:45:38 +1100
Message-ID: <a7u3nb$kci$1@lust.ihug.co.nz>

Well, here's the SQL I use to identify all constraints and their associated indexes (because the names can be different) for a specified table (in this case, imaginitively called 'squiggle'):

SELECT c.constraint_name,s.con#,cd.enabled,RPAD(o.object_name,20) object_name
FROM sys.con$ s, user_constraints c, sys.cdef$ cd, user_objects o WHERE c.constraint_type IN ('P','U')

AND c.table_name = 'SQUIGGLE'
AND c.constraint_name = s.name
AND s.con# = cd.con#

AND cd.enabled = o.object_id;

You could simply lop out the 'and c.table_name...' line to get a query that shows you all constraints and their associated indexes. Combine whichever version with what you've already got, and you're on your way, I think.

Notice that it does a search for both primary key and unique constraints. Both will have indexes associated with them that are undroppable.

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================

"Joe Sath" <dbadba62_at_hotmail.com> wrote in message
news:8Yvo8.26546$n8.651_at_nwrddc02.gnilink.net...


> I am writing a script, in which I need to drop all the indexes on that

> table.

>

> I use

> select index_name from dba_indexes where owner='$owner' and

> table_name='$table_name'

> and index_type='NORMAL'

>

> The problem is, sometimes the index is associated with a primary key, and

> the index can not be dropped, so the application will fail.

>

> Could someone tell me how should I improve the above sql so that any

indexes


> which is related to a constraint(primary key) will not be selected?

>

> Thanks for your help.

>

>

Received on Wed Mar 27 2002 - 21:45:38 CST