Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: What Indexes do I have???

Re: What Indexes do I have???

From: Michael Nolan <nolan_at_inetnebr.com>
Date: 1998/02/24
Message-ID: <6cv2ei$1fb$1@falcon.inetnebr.com>#1/1

cnelson_at_127.0.0.1 (Craig Nelson) writes:

>Anyone know how to list the names and other attributes of Indexes inside a
>table? I know I can DROP INDEX Mytable.mycolumn_index, and it works great
>if you know the name.

Depends on what you mean by 'other attributes'. Here's a short sqlplus script that will print all of your indexes, the tables they index, and the individual columns in the table that make up the index. It doesn't give storage parameters, the number of segments or bytes that the index takes, etc. (I have separate scripts to help me manage those.)

This script makes some assumptions about the length of table, index, and column names that might need to be changed, but it produces an 80 column wide report this way so I can print it on the laser printer or view it on screen.

I have a job scheduled to run at 7 AM every morning that produces a file with the information from this script and several others, it saves a lot of time when I want to look up something about a table or index during the day, and don't need up-to-the-moment information.

--
Mike Nolan

set feedback off
set pagesize 20

clear columns
clear breaks

column table_name format a20
column index_name format a20
column column_name format a16
column "Order" format 99999
column "Length" format 99999

break on index_name skip 1

select table_name, index_name, column_name, column_position "Order",
column_length "Length" from user_ind_columns
order by 1, 2, 4;
Received on Tue Feb 24 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US