인덱스 확인

SELECT IDX.OWNER, IDX.INDEX_NAME, IDX.TABLE_OWNER,
IDX.TABLE_NAME, IDX.TABLESPACE_NAME, IDX_COL.COLUMN_NAME
FROM DBA_INDEXES IDX, DBA_IND_COLUMNS IDX_COL
WHRE IDX.TABLE_NAME = 'EMP'
AND IDX.INDEX_NAME=IDX_COL.INDEX_NAME
ORDER BY INDEX_NAME;

 

SQL> desc dba_indexes;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 INDEX_TYPE VARCHAR2(27)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 TABLE_TYPE VARCHAR2(11)
 UNIQUENESS VARCHAR2(9)
 COMPRESSION VARCHAR2(8)
 PREFIX_LENGTH NUMBER
 TABLESPACE_NAME VARCHAR2(30)
 INI_TRANS NUMBER
 MAX_TRANS NUMBER
 INITIAL_EXTENT NUMBER
 NEXT_EXTENT NUMBER
 MIN_EXTENTS NUMBER
 MAX_EXTENTS NUMBER
 PCT_INCREASE NUMBER
 PCT_THRESHOLD NUMBER
 INCLUDE_COLUMN NUMBER
 FREELISTS NUMBER
 FREELIST_GROUPS NUMBER
 PCT_FREE NUMBER
 LOGGING VARCHAR2(3)
 BLEVEL NUMBER
 LEAF_BLOCKS NUMBER
 DISTINCT_KEYS NUMBER
 AVG_LEAF_BLOCKS_PER_KEY NUMBER
 AVG_DATA_BLOCKS_PER_KEY NUMBER
 CLUSTERING_FACTOR NUMBER
 STATUS VARCHAR2(8)
 NUM_ROWS NUMBER
 SAMPLE_SIZE NUMBER
 LAST_ANALYZED DATE
 DEGREE VARCHAR2(40)
 INSTANCES VARCHAR2(40)
 PARTITIONED VARCHAR2(3)
 TEMPORARY VARCHAR2(1)
 GENERATED VARCHAR2(1)
 SECONDARY VARCHAR2(1)
 BUFFER_POOL VARCHAR2(7)
 USER_STATS VARCHAR2(3)
 DURATION VARCHAR2(15)
 PCT_DIRECT_ACCESS NUMBER
 ITYP_OWNER VARCHAR2(30)
 ITYP_NAME VARCHAR2(30)
 PARAMETERS VARCHAR2(1000)
 GLOBAL_STATS VARCHAR2(3)
 DOMIDX_STATUS VARCHAR2(12)
 DOMIDX_OPSTATUS VARCHAR2(6)
 FUNCIDX_STATUS VARCHAR2(8)
 JOIN_INDEX VARCHAR2(3)

 SQL> desc dba_ind_columns;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 COLUMN_NAME VARCHAR2(4000)
 COLUMN_POSITION NOT NULL NUMBER
 COLUMN_LENGTH NOT NULL NUMBER
 CHAR_LENGTH NUMBER
 DESCEND VARCHAR2(4)

 SQL> desc v$object_usage;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 MONITORING VARCHAR2(3)
 USED VARCHAR2(3)
 START_MONITORING VARCHAR2(19)
 END_MONITORING VARCHAR2(19)

 SQL> spool off

토드 사용시 OWNER.TABLE_NAME 에 커서를 위치시키고 F4 를 누르면 별도의 Description을 확인할 수 있습닏.