[Oracle] 오라클DB 테이블 스페이스 관련 명령어

 

1. 테이블스페이스 전체 조회

SELECT * FROM DBA_TABLESPACES;

 

 

2. 테이블스페이스 파일 저장 경로 조회

SELECT * FROM DBA_DATA_FILES ORDER BY FILE_NAME;

 

 

3. 테이블스페이스 용량 조회

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

 

 

4. 테이블스페이스 용량 조회(MB)

SELECT A.TABLESPACE_NAME AS "테이블스페이스명"
       ,ROUND(MAXBYTES/1048576,2) AS "최대용량(MB)"
       ,ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) AS "할당용량(MB)"
       ,ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) AS "여유용량(MB)"
       ,ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) AS "사용량(MB)"
       ,ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) AS "잔여율(%)"
       ,100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) AS"사용률(%)"
FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) AS BYTES_ALLOC, SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) AS MAXBYTES
      FROM DBA_DATA_FILES F
      GROUP BY TABLESPACE_NAME) A
      ,(SELECT F.TABLESPACE_NAME, SUM(F.BYTES) AS 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) AS FREE_RATE
		,100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) AS USED_RATE
		,ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
FROM SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY TABLESPACE_NAME ASC;

 

 

5. 테이블스페이스 용량 조회(MB)

SELECT 
SUBSTR(A.TABLESPACE_NAME, 1, 30) AS "테이블스페이스명"
,ROUND(SUM(A.TOTAL)/1024/1024,1) AS "할당용량(MB)"
,ROUND(SUM(A.TOTAL)/1024/1024,1)-ROUND(SUM(A.S)/1024/1024,1) AS "사용용량(MB)"
,ROUND(SUM(A.S)/1024/1024,1) AS "여유용량(MB)"
,ROUND((ROUND(SUM(A.TOTAL)/1024/1024,1)-ROUND(SUM(A.S)/1024/1024,1))/ROUND(SUM(A.TOTAL)/1024/1024,1)*100,2) AS "사용률%"
FROM (SELECT TABLESPACE_NAME, 0 AS TOTAL, SUM(BYTES) AS S
	  FROM DBA_FREE_SPACE
	  GROUP BY TABLESPACE_NAME
	  UNION
	  SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL, 0
	  FROM DBA_DATA_FILES 
	  GROUP BY TABLESPACE_NAME) A
GROUP BY A.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME ASC;