Tibero 테이블 스페이스 용량 확인 방법

아래와 같이 테이블 스페이스를 확인 가능하다 그리고 자동 확장되고 있는 파일의 갯수도 한번에 확인 가능

 

-- create by soonpyo.jeong 2023.06.26

select DAY,
       tablespace TABLESPACE_NAME,
       status,
       total_mb "Total (MB)",
       used_mb "Used (MB)",
       free_mb "Free (MB)",
       usage "Usage (%)",
       '['||rpad(nvl(lpad('*', usage/5, '*'), '-'), 20, '-')||']' "Graph",
       stat "ENOUGH?",
       SEGMENT_SPACE_MANAGEMENT "ASSM?",
       AUTOEXTENS_CNT_FILE "AUTOEXTCNT",
       FILE_CNT
from   (select to_char(sysdate,'yyyy/mm/dd') DAY,
               total.ts tablespace,
               dbat.status status,
               round(total.mb) total_mb,
               round(total.mb - nvl(free.mb, 0)) used_mb,
               round(nvl(free.mb, 0)) free_mb,
               round((total.mb - nvl( free.mb, 0)) / total.mb * 100,2) usage,
                       case
                         when (total.mb - nvl( free.mb, 0)) / total.mb * 100 >= 85 and nvl( free.mb, 0) <= 1000 then 'Not Enough'
                         else 'Enough'
                       end stat,
               dbat.SEGMENT_SPACE_MANAGEMENT,
               total.AUTOEXTENS_CNT_FILE,
                total.FILE_CNT
        from   (select tablespace_name ts,sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) AUTOEXTENS_CNT_FILE,
               count(1) FILE_CNT,
                       sum(bytes)/1024/1024 mb
                from   dba_data_files
                group by tablespace_name) total,
          
         (select tablespace_name ts,
                       sum(bytes)/1024/1024 mb
                from   dba_free_space
                group by tablespace_name) free,
         dba_tablespaces dbat
        where  total.ts=free.ts(+)
        and    total.ts=dbat.tablespace_name
        )
where 1=1
order by "Usage (%)" desc;
;