오라클 테이블 row 길이, 테이블 사이즈, 인덱스 사이즈 조회하기

테이블 사이즈 조회하기

SELECT owner, segment_name, segment_type, sum(bytes)/1024/1024 as MB
FROM dba_segments
WHERE segment_type='TABLE'
AND owner = '소유자명'
GROUP BY owner, segment_name, segment_type
ORDER BY MB desc;


SELECT A.SEGMENT_NAME, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB", A.SEGMENT_TYPE
FROM DBA_SEGMENTS A, DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
AND A.SEGMENT_TYPE IN ('TABLE', 'TABLE_PARTITION')
AND A.OWNER = '소유자명'
GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
ORDER BY 2 DESC;

 

인덱스 사이즈 조회하기

SELECT A.SEGMENT_NAME, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB", A.SEGMENT_TYPE
FROM DBA_SEGMENTS A, DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
AND A.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION')
AND A.OWNER = '소유자명'
GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
ORDER BY 2 DESC;

 

오라클 테이블 row 길이 계산하기

select table_name, sum(data_length) byte
from all_tab_columns
where owner = '소유자명'
AND table_name = '테이블명'
group by table_name
order by byte desc;