오라클 유저의 인덱스와 코멘트 확인 쿼리

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 오라클 유저의 인덱스와 코멘트 확인 쿼리

테스트DB라 일반유저가 없어서 SYSTEM 유저의 인덱스를 조회함

SQL>
SELECT S.OWNER, S.TABLE_NAME, T.COMMENTS AS TAB_NM, U.INDEX_NAME, MAX(U.TABLESPACE_NAME) AS TABLESPACE_NAME,
MAX(U.STATUS) AS INDEX_STATUS, MAX(TO_CHAR(U.LAST_ANALYZED,'YYYYMMDD HH24:MI')) AS INDEX_ANAL_DT,
COUNT(V.COLUMN_NAME) AS INDEX_COL_CNT
FROM DBA_TABLES S, DBA_TAB_COMMENTS T,
DBA_INDEXES U, DBA_IND_COLUMNS V
WHERE S.OWNER IN ('SYSTEM')
AND T.OWNER = S.OWNER 
AND T.TABLE_NAME = S.TABLE_NAME 
AND T.COMMENTS IS NOT NULL
AND U.OWNER = S.OWNER
AND U.TABLE_NAME = S.TABLE_NAME 
AND V.INDEX_OWNER = U.OWNER 
AND V.INDEX_NAME = U.INDEX_NAME 
AND V.TABLE_OWNER = U.TABLE_OWNER
AND V.TABLE_NAME = U.TABLE_NAME
GROUP BY S.OWNER, S.TABLE_NAME, T.COMMENTS, U.INDEX_NAME
ORDER BY S.OWNER, S.TABLE_NAME, U.INDEX_NAME
/
 
SYSTEM       REPCAT$_REPSCHEMA        N-way replication information  REPCAT$_REPSCHEMA_DEST_IDX     SYSTEM     VALID      20191015 09:19         2
SYSTEM       REPCAT$_REPSCHEMA        N-way replication information  REPCAT$_REPSCHEMA_PRIMARY      SYSTEM     VALID      20191015 09:19         3
SYSTEM       REPCAT$_REPSCHEMA        N-way replication information  REPCAT$_REPSCHEMA_PRNT_IDX     SYSTEM     VALID      20191015 09:19         2
SYSTEM       REPCAT$_RESOLUTION        Description of all conflict re REPCAT$_RESOLUTION_F3_IDX      SYSTEM     VALID      20191015 09:19         2

참조 :