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;
'DevOps' 카테고리의 다른 글
[Docker] Windows에 Docker Desktop설치하기 (0) | 2023.06.07 |
---|---|
[Oracle] 오라클 삭제 후 COMMIT 한 데이터 복구 명령어 (0) | 2022.09.13 |
[Linux] 프로세스 관련 명령어 (0) | 2022.07.28 |
[Linux] 환경변수 관련 명령어 (0) | 2022.07.18 |
[Oracle] 오라클 SQLPLUS 원격 접속 방법 (0) | 2022.07.15 |