TABLE에 걸려 있는 INDEX 찾기

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 테이블에 걸려있는 인덱스 찾기, index 확인

/*************************************************************************/
/* show_index.sql USAGE: Show the indexes on a table */
/* */
/* This script prompts the user for the table owner and name then gets */
/* the indexed columns for any indexes on the table */
/*************************************************************************/
 
column index_name format A20
column column_name format A25
column column_position format 999 heading 'Pos'
column uniq format a5
set verify off
break on index_name skip 1
 
 
select C.index_name,substr(I.uniqueness,1,1) uniq, C.column_name,
C.column_position
from dba_ind_columns C
,dba_indexes I
where C.table_owner = upper('&table_owner')
and C.table_name = upper('&table_name')
and C.index_owner = I.owner
and C.index_name = I.index_name
order by 2 desc,1,4
/
 
Enter value for table_owner: JSH
Enter value for table_name: EMP
 
INDEX_NAME         UNIQ  COLUMN_NAME              Pos
-------------------- ----- ------------------------- ----
PK_EMP             U       EMPNO            1
 
 
1 row selected.

참조 : otn