TABLESPACE 용량 조회 > 데이터베이스

본문 바로가기
사이트 내 전체검색


회원로그인

데이터베이스

Oracle | TABLESPACE 용량 조회

페이지 정보

작성자 100K5 작성일19-08-14 15:22 조회24,033회 댓글0건

본문

1. 테이블스페이스 정보 조회
SELECT * FROM DBA_TABLESPACES;

 

2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         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;

 

3. 테이블스페이스별 현황 확인 쿼리문(MB 단위)
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
  (
  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-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;

 

4.  테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)
SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) 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)) 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;


5. 테이블 용량 조회
 SELECT
  owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
 FROM
  (SELECT segment_name table_name, owner, bytes
  FROM dba_segments
  WHERE segment_type in ('TABLE','TABLE PARTITION')
  UNION ALL
  SELECT i.table_name, i.owner, s.bytes
  FROM dba_indexes i, dba_segments s
  WHERE s.segment_name = i.index_name
  AND s.owner = i.owner
  AND s.segment_type in ('INDEX','INDEX PARTITION')
  UNION ALL
  SELECT l.table_name, l.owner, s.bytes
  FROM dba_lobs l, dba_segments s
  WHERE s.segment_name = l.segment_name
  AND s.owner = l.owner
  AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')
  UNION ALL
  SELECT l.table_name, l.owner, s.bytes
  FROM dba_lobs l, dba_segments s
  WHERE s.segment_name = l.index_name
  AND s.owner = l.owner
  AND s.segment_type = 'LOBINDEX')

 ---WHERE owner in UPPER('&owner')
 GROUP BY table_name, owner
 HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
 ORDER BY SUM(bytes) desc

 

출처 : https://javafactory.tistory.com/83

댓글목록

등록된 댓글이 없습니다.


사이트소개 개인정보취급방침 서비스이용약관 Copyright © kkujunhee.net All rights reserved.
상단으로

개인정보관리책임자 : 관리자

모바일 버전으로 보기