[ORACLE] 테이블 스페이스 확인 쿼리

테이블 스페이스의 정보를 확인 하는 쿼리다.

SELECT A.TABLESPACE_NAME,
	   ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
	   ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
	   ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
	   ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
	   100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
	   ROUND(MAXBYTES/1048576,2) MAX_SIZE
FROM   ( SELECT F.TABLESPACE_NAME,
				SUM(F.BYTES) BYTES_ALLOC,
				SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
		 FROM DBA_DATA_FILES F
		 GROUP BY TABLESPACE_NAME) A,
	   ( SELECT F.TABLESPACE_NAME,
				SUM(F.BYTES)  BYTES_FREE
		 FROM DBA_FREE_SPACE F
		 GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
UNION
SELECT TABLESPACE_NAME,
	   ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
	   ROUND(SUM(BYTES_FREE) / 1048576,2),
	   ROUND(SUM(BYTES_USED) / 1048576,2),
	   ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
	   100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
	   ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
FROM   SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY 1;