Re: Display an index

From: Oracleguru <oracleguru_at_mailcity.com>
Date: 1998/09/04
Message-ID: <01bdd825$b0f0c2c0$a504fa80@mndnet>#1/1

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

Karl E. Taylor <ktaylor_at_dragon.illusions.com> wrote in article <35ED7AF7.451B1B9E_at_dragon.illusions.com>...
> Perhaps you folks can help.
>
> I have several users and DBA's for remote clients that keep talking
> about doing a describe on an index before dropping and recreating it.
> This of of course on our 7.3 systems. Now I always learned that the
> only way to actually see what an index was made up from was to select
> the index_name from all_ind_column and it would show you what fields
> made up the index. The question is, is there a describe index
> _index_name_ type function in Oracle 7.3.x to 8x? It sure would make my
> life a lot easier.
>
> Thanks.
>
> Please post all replays to the news group, do not send e-mail at this
> time.
> --
> ________________________________________________________________________
>
> Karl E. Taylor CEO & UNIX Systems Analyst
>
> Desert Dragon SOHO Solutions ktaylor_at_dragon.illusions.com
>
> http://www.illusions.com/ddsoho
> ________________________________________________________________________
> Dr. Lao "You know what wisdom is?"
> Little boy "No."
> Dr. Lao "Wise answer."
>
Received on Fri Sep 04 1998 - 00:00:00 CDT