Obsidian/Recognition/Programing/Tibero/테이블 스페이스 관련.md

-- 테이블 스페이스 목록
SELECT tablespace_name FROM dba_tablespaces;

-- 테이블 스페이스 경로, 파일 이름
SELECT file_id, tablespace_name, file_name from dba_data_files;

-- 할당량/여유공간 확인

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
order by 1