미사용 테이블

DB CAFE

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

미사용 테이블 조회[편집]

SELECT  A.SEGMENT_NAME
      , A.SEGMENT_TYPE
      , NVL(B.SUM_VALUE,0)
FROM    (

             SELECT SEGMENT_NAME,

             SEGMENT_TYPE

             FROM DBA_SEGMENTS

             WHERE OWNER = 'TEST'

             AND SEGMENT_TYPE ='TABLE'

        ) A

      , (

             SELECT 

                    OBJECT_NAME

                  , OBJECT_TYPE

                  , SUM(VALUE) sum_value

             FROM   V$SEGMENT_STATISTICS

             WHERE  OWNER = 'TEST'

             AND    OBJECT_TYPE='TABLE'

             AND    (STATISTIC_NAME LIKE '%read%' OR STATISTIC_NAME LIKE '%write%' )

             GROUP BY OBJECT_NAME, OBJECT_TYPE

        ) B

WHERE   A.SEGMENT_NAME = B.OBJECT_NAME(+)

AND     A.SEGMENT_TYPE = B.OBJECT_TYPE(+)

ORDER BY A.SEGMENT_TYPE , A.SEGMENT_NAME;

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=미사용_테이블&oldid=3604"

Oracle