oracle 점검

1. 테이블스페이스 사용량 체크

select ts_name, sum(total)/1024 "total(MB)", sum(used)/1024 "used(MB)", sum(free)/1024 "free(MB)",

sum(used)*100/sum(total) "free %"

from

( SELECT b.file_name fileName,--"FILE_NAME", -- DataFile Name

b.tablespace_name ts_name,--"TABLESPACE_NAME", -- TableSpace Name

b.bytes / 1024 total,--"TOTAL SIZE(KB)", -- 총 Bytes

((b.bytes - sum(nvl(a.bytes,0)))) / 1024 used,--"USED(KB)", -- 사용한 용량

(sum(nvl(a.bytes,0))) / 1024 free,--"FREE SIZE(KB)", -- 남은 용량

(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 free_rate--"FREE %" -- 남은 %

FROM DBA_FREE_SPACE a, DBA_DATA_FILES b

WHERE a.file_id(+) = b.file_id

GROUP BY b.tablespace_name, b.file_name, b.bytes)

group by ts_name

order by "free %" desc;

​

-- 특정TableSpace에 속한 Table별 Size 보기

select segment_name "Table Name", tablespace_name "Tablespace"

, sum(bytes) / 1000000 "Table Size(MBytes)", sum(bytes) / 1000000000 "Table Size(GBytes)"

from user_segments

where tablespace_name ='TableSpace 명'

and segment_type like 'TABLE%'

group by segment_name,tablespace_name

order by 4 desc;

​

-- index tablespace일경우

select * from user_segments

where TABLESPACE_NAME = '인덱스명'

order by bytes desc--blocks;