Re: Find Index definition for an Oracle table

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Thu, 27 Aug 1998 13:58:30 GMT
Message-ID: <01bdd1d2$83c05800$a504fa80@mndnet>

Hi  

Try this script, you will get indexes on all tables in a user's schema. You may need to log into SQL*Plus as that user or have access to his tables. User_id is like OPS$XYZ.

Good luck !!!

REM

REM     Author     : Oracleguru
REM     Date       : 01/31/95
REM     File Name  : describe_indices.sql
REM     Usage      : On sqlplus prompt enter:
REM                      @describe_indices user_id
REM
REM     Description: Describes Indices in a User's Schema.
REM                  It is assumed that you can access that user's objects.

REM
set newpage 0 pagesize 79 linesize 132 space 3 feedback off set verify off pause off
break on table_name on index_name skip 1 column today new_value ttitle_today noprint column today_time new_value ttitle_today_time noprint

column   table_name         format a31
column   index_name         format a31
column   column_name        format a31
column   seq                format 999
column   uniqueness         format a10

prompt
prompt Description of Indices is being sent to your default printer and prompt file $HOME/rep/describe_indices.lst prompt
prompt Please wait ....
set termout off
ttitle left ttitle_today center 'Description of Indices for user' -  right 'Page ' format 90 sql.pno skip 1 -  left ttitle_today_time center &1 skip 1 -  left 'describe_indices.sql' skip 2
select to_char(sysdate,'DD-MON-YYYY') today,

         to_char(sysdate,'HH:MI:SS AM') today_time   from dual;
spool $HOME/rep/describe_indices.lst

select   a.table_name table_name,
         a.index_name index_name,
         a.column_name,
         a.column_position seq,
         b.uniqueness
  from   all_ind_columns a,
         all_indexes     b
 where   a.table_owner = upper('&1')
   and   a.index_name  = b.index_name

   and a.table_owner = b.table_owner
order by a.table_name, a.index_name
/
prompt
prompt

prompt .                                                ********** -

END OF REPORT **********
spool off
exit

timbedow_at_my-dejanews.com wrote in article <6s2dl5$qip$1_at_nnrp1.dejanews.com>...
> I need to know how to find an index definition for an Oracle table. I see
it
> in user_indexes, but I want to know which columns it is indexing.
> Specifically, I want to know if a specific column is indexed within a
table.
>
> Please respond to the email below ASAP!
>
> TIA,
>
> Tim.
> tim.bedow_at_NO_SPAMps.net
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Thu Aug 27 1998 - 08:58:30 CDT