[oracle] tablespace 관련 정보 조회

1. DBA 권한으로 접속

[oracle@centos ~]$ sqlplus / as sysdba

2. 포맷

SQL> COL TABLESPACE_NAME FORMAT A10

3. 용량 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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;

TABLESPACE CURRENT_SIZE FREE_SIZE USED_SIZE FREE_RATE USED_RATE MAX_SIZE ---------- ------------ ---------- ---------- ---------- ---------- ---------- EXAMPLE 100 21.25 78.75 21.25 78.75 32767.98 SYSAUX 490 28.13 461.88 5.74 94.26 32767.98 SYSTEM 690 9.06 680.94 1.31 98.69 32767.98 TEMP 29 0 29 0 100 29 UNDOTBS1 90 70.19 19.81 77.99 22.01 32767.98 USERS 5 .94 4.06 18.75 81.25 32767.98 6 rows selected.

2. 관련 테이블

2.1 DBA_TABLESPACES 테이블스페이스 목록

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5054.htm

1	SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

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

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3135.htm

1	SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

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

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3179.htm

1	SELECT TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_FREE_SPACE;