오라클 테이블 스페이스 용량 확인

오라클 테이블 스페이스 용량 확인

 

SELECT 

A.FILE_ID AS 파일번호,

A.TABLESPACE_NAME AS 테이블스페이스명,

A.FILE_NAME AS 파일경로,

A.TOTAL_SPACE_BYTES  AS 총크기, 

(A.TOTAL_SPACE_BYTES - B.FREE_SPACE_BYTES) AS 사용공간,

B.FREE_SPACE_BYTES AS 여유공간,

TO_CHAR( (B.FREE_SPACE_BYTES / A.TOTAL_SPACE_BYTES * 100) , '999.99')||'%'  AS 여유공간비율
FROM
   (
   SELECT FILE_ID,TABLESPACE_NAME,FILE_NAME,SUM(NVL(BYTES,0)) AS TOTAL_SPACE_BYTES
   FROM DBA_DATA_FILES
   GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME
   ) A,
   (
   SELECT FILE_ID,TABLESPACE_NAME,SUM(NVL(BYTES,0)) AS FREE_SPACE_BYTES
   FROM DBA_FREE_SPACE
   GROUP BY FILE_ID, TABLESPACE_NAME
   ) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.FILE_ID = B.FILE_ID
ORDER BY A.FILE_ID;