미사용 테이블 조회
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;