|
이용안내
| 이용안내
asd
김상백 날짜 2021.05.21 (18:34)   조회수 : 581   추천 : 0  


SELECT
"Check Time"
,TS_INFO.tablespace_name
,TS_INFO."TOTAL SIZE(MB)"
,TS_INFO."USED(MB)"
,TS_INFO."AVAIL(MB)" "Tot_AVAIL(MB)"
,TO_CHAR((MAX(DFS.BYTES)/1024/1024),'999,990.999') "Act_AVAIL(MB)"  -- max Extent size on The tablespace
,TS_INFO."USED %"
,TS_INFO."FREE %"
,TS_INFO."STATUS"
FROM (
SELECT
SYSDATE "Check Time"
,tablespace_name
,sum(total_size)/1024 "TOTAL SIZE(MB)"
,TO_CHAR(round((sum(used)/1024),4),'999,990.999') "USED(MB)"
,TO_CHAR((sum(total_size)/1024)-(round((sum(used)/1024),4)),'999,990.999') "AVAIL(MB)"
,TO_CHAR(round((sum(used)/sum(total_size)),4)*100,'990.99') "USED %"
,TO_CHAR(round(1-(sum(used)/sum(total_size)),4)*100,'909.99') "FREE %"
,CASE WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '10' THEN ' Alert'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '15' THEN ' Warning'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '30' THEN ' Normal'
      ELSE ' Good' END "STATUS"
FROM (
SELECT
                  b.tablespace_name , -- tablespace Name
                  b.bytes / 1024 total_size, -- 총 Bytes
                  ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 used, -- 사용한 용량
                  (sum(nvl(a.bytes,0))) / 1024 free_size -- 남은 용량
                  FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
AND
b.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces)
                  GROUP BY b.tablespace_name, b.file_name, b.bytes
)
GROUP BY tablespace_name
) TS_INFO,
DBA_FREE_SPACE DFS 
WHERE TS_INFO.TABLESPACE_NAME = DFS.TABLESPACE_NAME
GROUP BY TS_INFO.tablespace_name,"TOTAL SIZE(MB)","USED(MB)","AVAIL(MB)","USED %","FREE %","STATUS","Check Time"
ORDER BY "FREE %"; 


현재 0개의 댓글 이 등록되어 있습니다. [닫기]
 
번호 제목 작성자 조회수 등록일
3
asd   
김상백 582 2021.05.21
2 시스템관리자 4849 2020.11.24
 1
 이름   제목