Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do you find a index which belongs to a primary key
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#
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...Received on Wed Mar 27 2002 - 21:45:38 CST
> 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.
>
>