[ORACLE]Tablespace 관련 쿼리 모음

Category: Oracle | March 12, 2019

출처 : FreeLife의 저장소

Tablespace 정보 조회

SELECT *
FROM
    DBA_TABLESPACES;

TABLESPACE 별 용량 조회

SELECT
  SUBSTR(a,tablespace_name,1,30) TABLESPACE,
  ROUND(SUM(A.TOTAL1)/1024/1024,1) "TOTAL MB",
  ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1) "USED MB",
  ROUND(SUM(A.SUM1)/1024/1024,1)"FREE MB",
  ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))/ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED %"
FROM
  (SELECT
     TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB, COUNT(BYTES) CNT
   FROM
     DBA_FREE_SPACE
   GROUP BY
     TABLESPACE_NAME
   UNION
   SELECT
     TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
   FROM
     DBA_DATA_FILES
   GROUP BY
     TABLESPACE_NAME) A
GROUP BY
  A.TABLESPACE_NAME
ORDER BY
  TABLESPACE;

TABLESPACE 별 현황 확인

SELECT
  TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBYTES, RESULT/1024 AS USE_MBYTES
FROM
  (SELECT
     E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES, (EBYTES-SUM(F.BYTES)) RESULT
   FROM
     DBA_DATA_FILES E, DBA_FREE_SPACE F
   WHERE
     E.FILE_ID = F.FILE_ID
   GROUP BY
     E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
  ) A;