Oracle でテーブルにはられているインデックスを調べるSQL

• show_indexes.sql

set pagesize 10000
set linesize 130
col table_name for a40
col index_name for a40
col column_name for a20
col column_position for 9999
select index_name, table_name, column_name, column_position 
	from user_ind_columns 
		where table_name = '&table_name'
		order by index_name, column_position;

• 実行結果

SQL> conn scott/tiger
SQL> @show_indexes.sql
Enter value for table_name: EMP
old   3:                where table_name = '&table_name'
new   3:                where table_name = 'EMP'

INDEX_NAME                               TABLE_NAME                               COLUMN_NAME          COLUMN_POSITION
---------------------------------------- ---------------------------------------- -------------------- ---------------
PK_EMP                                   EMP                                      EMPNO                              1

追記(2010/03/25):
複数のテーブルのインデックスをまとめて確認する SQL。

set pagesize 10000
set linesize 200
col table_name for a40
col index_name for a40
col column_name for a30
col column_position for 9999
select index_name, table_name, column_name, column_position 
	from user_ind_columns 
		where table_name in ('EMP','DEPT')
		order by index_name, column_position;