SELECT A.TABLESPACE_NAME,
ROUND(SUM(A.BYTES) / (1024 * 1024)) || 'M' "전체",
ROUND(SUM(B.FREES) / (1024 * 1024)) || 'M' "여유"
FROM
(SELECT FILE_ID, TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY FILE_ID, TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) FREES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND
A.FILE_ID = B.FILE_ID
GROUP BY A.TABLESPACE_NAME
datafile별로
SELECT A.TABLESPACE_NAME "테이블스페이스명", (A.BYTES - B.FREE) "사용공간", B.FREE "여유 공간", A.BYTES "총크기", TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간" FROM ( SELECT FILE_ID, TABLESPACE_NAME, SUBSTR(FILE_NAME,1,200) FILE_NM, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY FILE_ID,TABLESPACE_NAME,SUBSTR(FILE_NAME,1,200) ) A, ( SELECT TABLESPACE_NAME, FILE_ID, SUM(NVL(BYTES,0)) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID ) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.FILE_ID = B.FILE_ID
datafile별로
SELECT A.TABLESPACE_NAME "테이블스페이스명", (A.BYTES - B.FREE) "사용공간", B.FREE "여유 공간", A.BYTES "총크기", TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간" FROM ( SELECT FILE_ID, TABLESPACE_NAME, SUBSTR(FILE_NAME,1,200) FILE_NM, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY FILE_ID,TABLESPACE_NAME,SUBSTR(FILE_NAME,1,200) ) A, ( SELECT TABLESPACE_NAME, FILE_ID, SUM(NVL(BYTES,0)) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME,FILE_ID ) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.FILE_ID = B.FILE_ID