오라클 용량 확인(전체 용량, 테이블별 용량, 테이블스페이스별 용량)

오라클 DB 용량 조회하기 쿼리문

전체 용량(용량, 사용량, 남은용량)
DB 전체 용량 확인 : DBA_DATA_FILES​

SELECT SUM(bytes) FROM DBA_DATA_FILES;
SELECT SUM(bytes)/1024/1024/1024||'GB' FROM DBA_DATA_FILES;
DB 전체 사용량 확인 : DBA_SEGMENTS

SELECT SUM(bytes) FROM DBA_SEGMENTS;
SELECT SUM(bytes)/1024/1024/1024||'GB' FROM DBA_SEGMENTS;
DB 전체 여유량 확인 : DBA_FREE_SPACE

SELECT SUM(bytes) FROM DBA_FREE_SPACE;
SELECT SUM(bytes)/1024/1024/1024||'GB' FROM DBA_FREE_SPACE;
​

테이블별 용량
유저의 테이블 사용량 확인 : USER_TABLES

SELECT
	TABLE_NAME					AS 테이블명
	, NUM_ROWS					AS 행의수
	, NUM_ROWS * AVG_ROW_LEN	AS BYTE
	, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 2)			AS MB
	, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 1024), 2)	AS GB
FROM
	USER_TABLES
WHERE
	TABLE_NAME = '테이블명';

SELECT
	OWNER
	, SEGMENT_NAME
	, SEGMENT_TYPE
	, BYTES / 1024 / 1024 as MB
FROM
	DBA_SEGMENTS
WHERE
	OWNER = '계정명'
	AND SEGMENT_NAME = '테이블명'
	AND SEGMENT_TYPE = 'TABLE';
​

테이블스페이스별 용량
테이블스페이스 사용량과 남은 용량 확인

 - 전체 용량 : DBA_DATA_FILES

 - 남은 용량 : DBA_FREE_SPACE

-- 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;

-- 2. 자세하게 얻기
SELECT
	A.tablespace_name
	, total || 'MB'			TOTAL_SIZE
	, total - free || 'MB'	USED_SIZE
	, free || 'MB'			FREE_SIZE
	, ROUND((total - free) / total * 100, 2) || '%' USED_RATE
FROM
	(SELECT
		tablespace_name
		, ROUND(SUM(bytes) / 1024 / 1024, 1) free
	FROM
		DBA_FREE_SPACE
	GROUP BY
		tablespace_name) A,
	(SELECT 
		tablespace_name
		, ROUND(SUM(bytes) / 1024 / 1024, 1) total
	FROM
		DBA_DATA_FILES
	GROUP BY
		tablespace_name) B
WHERE
	A.tablespace_name = B.tablespace_name
ORDER BY
	A.tablespace_name;

-- 3. 자세하게 얻기
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;

출저 : [Oracle/Tibero]DB 용량 확인하기(전체 용량, 테이블별 용량, 테이블스페이스별 용량) : 네이버 블로그 (naver.com)