[Oracle] TABLE, INDEX 실 데이터 size 구하기 (04.05.11 18:26)

수정하여 사용하기에 따라 TABLESPACE의 실제 전체크기 등을 쉽게 구할 수 있다.

TABLE size 구하기

set pagesize 9999 col owner format a10 col tablespace_name format a20 col table_name format a30 col mb format 99990.99 SELECT OWNER, TABLESPACE_NAME, TABLE_NAME, NUM_ROWS*AVG_ROW_LEN/1024/1024 as MB FROM DBA_TABLES WHERE OWNER IN ('ADPORTAL','INTRADBA','UWDBA','WEBSALES') ORDER BY OWNER, TABLESPACE_NAME, TABLE_NAME;

INDEX size 구하기
db_block_size를 LEAF_BLOCKS과 곱한다
select value from v$parameter where name = 'db_block_size' set pagesize 9999 col owner format a10 col tablespace_name format a20 col index_name format a30 col mb format 99990.99 SELECT OWNER, TABLESPACE_NAME, INDEX_NAME, LEAF_BLOCKS*8192/1024/1024 as MB FROM DBA_INDEXES WHERE OWNER IN ('ADPORTAL','INTRADBA','UWDBA','WEBSALES') ORDER BY OWNER, TABLESPACE_NAME, INDEX_NAME;


Comments (0)