Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: What Indexes do I have???
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