TABLESPACE USAGE

--TABLESPACE CHECK

SELECT TABLESPACE_NAME     AS "테이블스페이스명",

EXTEND_BYTES/1024/1024   AS "총 할당량(MB)" ,

USED_BYTES/1024/1024      AS "총 사용량(MB)",

ROUND(((USED_BYTES)/EXTEND_BYTES)*100,2) || '%'    AS "여유공간(MB)",

FREE_BYTES/1024/1024

ROUND((1-((USED_BYTES)/EXTEND_BYTES))*100,2) || '%' AS "여유공간률"

FROM (SELECT T1.TABLESPACE_NAME,

T1.EXTEND_BYTES,

T1.EXTEND_BYTES - T2.FREE_BYTES USED_BYTES,

T2.FREE_BYTES

FROM (SELECT TABLESPACE_NAME, SUM(BYTES) EXTEND_BYTES

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T1,

(SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES

FROM DBA_FREE_SPACe

GROUP BY TABLESPACE_NAME) T2

WHERE T2.TABLESPACE_NAME = T1.TABLESPACE_NAME)

ORDER BY 6;