[Oracle] 테이블스페이스 용량 확인 쿼리 (콘솔, sqlplus)

1. 용량 확인 쿼리 

SELECT A.TABLESPACE_NAME,

               ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,

               ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,

               ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,

               ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,

               100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,

               ROUND(MAXBYTES/1048576,2) MAX_SIZE

        FROM   ( SELECT F.TABLESPACE_NAME,

                        SUM(F.BYTES) BYTES_ALLOC,

                        SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES

                 FROM DBA_DATA_FILES F

                 GROUP BY TABLESPACE_NAME) A,

               ( SELECT F.TABLESPACE_NAME,

                        SUM(F.BYTES)  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) FREE_RATE,

               100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,

               ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)

        FROM   SYS.V_$TEMP_SPACE_HEADER

        GROUP BY TABLESPACE_NAME

        ORDER BY 1;

 

 

2. 관련 테이블

2.1 DBA_TABLESPACES 테이블스페이스 목록

# SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

 

2.2 DBA_DATA_FILES 테이블스페이스 파일 목록

# SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

 

2.3 DBA_FREE_SPACE 테이블스페이스 잔여 공간

# SELECT TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_FREE_SPACE;

 

 

 

[테이블스페이스 확인]

# SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM dba_data_files;

[사용자 확인]
# SELECT USERNAME, DEFAULT_TABLESPACE FROM dba_users

 

 

 

[출처] 테이블스페이스(tablespace), 사용자(user) 확인

[출처] https://blueray21.tistory.com/32