Oracle DB TableSpace(테이블스페이스) 용량 조회

***** TableSpace 조회 *****

SELECT * FROM DBA_TABLESPACES;

결과:

 

 

 

*****TableSpace 용량 조회1(간단조회)*****

SELECT
	A.TABLESPACE_NAME
	, 100 - ROUND((FREE/TOTAL) * 100, 0) || '%'	USED_RATE
	, TO_CHAR(TOTAL, '999,999') || 'MB'			TOTAL_SIZE
	, TO_CHAR(FREE, '999,999') || 'MB'			FREE_SIZE
FROM
	(SELECT
		TABLESPACE_NAME
		, ROUND(SUM(BYTES) / 1024000, 1) FREE
	FROM
		DBA_FREE_SPACE
	GROUP BY
		TABLESPACE_NAME) A,
	(SELECT 
		TABLESPACE_NAME
		, ROUND(SUM(BYTES) / 1024000, 1) TOTAL
	FROM
		DBA_DATA_FILES
	GROUP BY
		TABLESPACE_NAME) B
WHERE
	A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY
	A.TABLESPACE_NAME;

결과:

 

 

*****TableSpace 용량 조회2 (상세조회)*****

SELECT
	A.TABLESPACE_NAME		"테이블스페이스명"
	, A.FILE_NAME			"파일경로"
	, (A.BYTES - B.FREE)	"사용공간"
	, B.FREE				"여유 공간"
	, A.BYTES				"총크기"
	, TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%'	"여유공간"
FROM
	(
	SELECT
		FILE_ID
		, TABLESPACE_NAME
		, FILE_NAME
		, SUBSTR(FILE_NAME,1,200) FILE_NM
		, SUM(BYTES) BYTES
	FROM 
		DBA_DATA_FILES
	GROUP BY 
		FILE_ID
		, TABLESPACE_NAME
		, FILE_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;

결과: