Re: Indexes - how to get details?

From: Halvor F. Hanto <halvorh_at_telepost.no>
Date: 1995/09/11
Message-ID: <43285c$oth_at_nms.telepost.no>#1/1

In article <810847346snz_at_ahardy.demon.co.uk>, aph_at_ahardy.demon.co.uk says...
>
>Hi,
>
>I'm an Oracle newbie (only just read the 12th manual...)
>
>Is there an easy way to determine what columns of what tables have been
>indexed - in such a manner as to make it possible to replicate the
>index on another database i.e sql create statements?
>
>The closest I've seen so far is the indexfile option on database import.
>
>I *think* that I want to drop all my indexes before I perform a large
>data load (using sqlload) - but I need an easy way to put all the
>indexes back.
>
>I'm using Oracle 7.1.6 on HP UX 10.
>
>Andy
>--
>Andy Hardy (Internet: aph_at_ahardy.demon.co.uk, CIS: 100015,2603)
>PGP key available on request
>===============================================================
>Think honk if you're a telepath.

Hi!
Is this what you're looking for ? The query does not perform so well, but it works !

select decode(c.column_position

             ,1,'create '||decode(i.uniqueness
                                 ,'NONUNIQUE',null
                                 ,i.uniqueness||' ')||
                'index '||i.index_name||
                ' on '||i.table_name||
                ' ('
             ,','
             )||
       c.column_name||
       decode(c.column_position
             ,c2.column_position,') storage ('||
                                 ' initial '||i.initial_extent||
                                 ' next '||i.next_extent||
                                 ' min_extents '||i.min_extents||
                                 ' max_extents '||i.max_extents||
                                 ' pctincrease '||i.pct_increase||
                                 ' )'||
                                 ' tablespace '||i.tablespace_name||
                                 ' initrans '||i.ini_trans||
                                 ' maxtrans '||i.max_trans||
                                 ' pctfree '||i.pct_free||
                                 ';'
             ,null
             )
from   user_ind_columns c2
      ,user_ind_columns c
      ,user_indexes i

where c.table_name = i.table_name
and c.index_name = i.index_name

and    c2.index_name = i.index_name
and    c2.table_name = i.table_name
and    c2.column_position =
           (
            select max(sc.column_position)
            from   user_ind_columns sc
            where  sc.index_name = i.index_name
            and    sc.table_name = i.table_name
           )

and i.table_owner = '&&DBSchema'
order by

       i.table_name
      ,i.index_name
      ,c.column_position

/

Rgds.
Halvor Halvor Received on Mon Sep 11 1995 - 00:00:00 CEST