[ORACLE] Tablespace Size Check

SELECT TABLESPACE_NAME ,
          ((EXTEND_BYTES/1024)/1024)||'M' EXT,
          ((USED_BYTES/1024)/1024)||'M' USED,
          ROUND(((USED_BYTES)/EXTEND_BYTES)*100,2) || '%' USE_RATE,
          ((FREE_BYTES/1024)/1024) FREE,
          ROUND((1-((USED_BYTES)/EXTEND_BYTES))*100,2) || '%' FREE_RATE
 FROM ( SELECT T1.TABLESPACE_NAME,
                      T1.EXTEND_BYTES,
                      T1.EXTEND_BYTES - T2.FREE_BYTES USED_BYTES,
                      T2.FREE_BYTES
            FROM ( SELECT TABLESPACE_NAME,
                                 SUM(BYTES) EXTEND_BYTES
                        FROM DBA_DATA_FILES
                      GROUP BY TABLESPACE_NAME ) T1,
                     (SELECT TABLESPACE_NAME,
                                SUM(BYTES) FREE_BYTES
                       FROM DBA_FREE_SPACE
                     GROUP BY TABLESPACE_NAME) T2
          WHERE T2.TABLESPACE_NAME = T1.TABLESPACE_NAME )
 ORDER BY TABLESPACE_NAME

SELECT A.TABLESPACE_NAME,
          ROUND(A.BYTES_ALLOC / 1024 / 1024, 2)||'M' CURRENT_SIZE,
          ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2)||'M' FREE_SIZE,
          ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2)||'M' USED_SIZE,
          ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2)||'M' FREE_RATE,
          100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2)||'M' USED_RATE,
          ROUND(MAXBYTES/1048576,2)||'M' 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 파일 위치 포함 보기
SELECT A.TABLESPACE_NAME                      Tablespace_name,
          A.FILE_NAME                                 File_path,
          ROUND((A.BYTES - NVL(B.FREE,0))/1024/1024,0)||'M'  Used_Size,
          ROUND(NVL(B.FREE,0)/1024/1024,0)||'M'                 Free_Size,
          ROUND(A.BYTES/1024/1024,0)||'M'                        Tot_Size,
          TO_CHAR( (NVL(B.FREE,0) / A.BYTES * 100) , '999.99')||'%'  Free_Size_Per,
          A.AUTOEXTENSIBLE
 FROM ( SELECT FILE_ID,
                      TABLESPACE_NAME,
                      FILE_NAME,
                      SUBSTR(FILE_NAME,1,200) FILE_NM,
                      AUTOEXTENSIBLE,
                      SUM(BYTES) BYTES
             FROM DBA_DATA_FILES
            GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,
                      SUBSTR(FILE_NAME,1,200),AUTOEXTENSIBLE ) 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 A.TABLESPACE_NAME


*Tablespace AutoExtened 여부 확인
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE
 FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME



* Tablespace 확장
Alter TABLESPACE users
ADD DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\USERS03.DBF ' SIZE 2000M
AUTOEXTEND {ON|OFF};


* 이미 생성된 Tablespace에 auto extend On시
ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\USERS02.DBF'
AUTOEXTEND ON;