[오라클] TABLESPACE 관련 스크립트

테이블스페이스 전체 용량과 현재 사용량을 조회하는 쿼리입니다: 

REM NAME  : 테이블 스페이스의 이름.
REM FILECNT : 해당 테이블 스페이스의 파일 수.
REM TOTAL(MB) : 총 파일시스템에 할당된 용량(단위, 메가 바이트 ).
REM USED(MB) : 사용된 용량( 단위 , 메가바이트 ).
REM FREE(MB) : 여유공간 ( 단위, 메가바이트 ),
REM MAXBYTE(MB ) : 자동 증가할 수 있는용량 ( 단위, 메가바이트 )

COL NAME FOR A15

SELECT tbs.tablespace_name as "NAME", tbs.FILE_NAME "FILE_NAME", tbs.FILECNT as "FILECNT", tbs.TOTAL as "TOTAL(MB)"
              , (tbs.TOTAL - nvl(fr.FREE,0)) as "USED(MB)" ,  nvl(fr.FREE,0) as "FREE(MB)"
              , nvl(tbs.MAX,0) as "MAXBYTE(MB)",  nvl(tbs.TOTAL- fr.FREE,0)/tbs.TOTAL *100 as "USED(%)"
FROM   (select tablespace_name, sum(bytes/1024/1024) as "FREE"
                   from dba_free_space
                  group by tablespace_name) fr,
                (select tablespace_name, FILE_NAME, sum(bytes/1024/1024) as "TOTAL", count(*) as "FILECNT",
                  sum(maxbytes/1024/1024) as "MAX"
                  from dba_data_files
                  group by tablespace_name, FILE_NAME) tbs
WHERE tbs.tablespace_name = fr.tablespace_name (+)
ORDER BY "NAME";

 

예시 결과:

NAME            FILE_NAME                                        FILECNT  TOTAL(MB)   USED(MB)   FREE(MB) MAXBYTE(MB)    USED(%)
--------------- --------------------------------------------- ---------- ---------- ---------- ---------- ----------- ----------
SYSAUX          /u01/app/oracle/oradata/ORCL/sysaux01.dbf              1        660     603.25      56.75  32767.9844 91.4015152
SYSTEM          /u01/app/oracle/oradata/ORCL/system01.dbf              1        900     895.25       4.75  32767.9844 99.4722222
UNDOTBS1        /u01/app/oracle/oradata/ORCL/undotbs01.dbf             1        345      24.25     320.75  32767.9844 7.02898551
USERS           /u01/app/oracle/oradata/ORCL/users01.dbf               1          5       4.25        .75  32767.9844         85

 

TABLESPACE별 사용량 보기

select 
  tablespace_name, 
  round(used_space/1024/1024, 2) "used_space(mb)", 
  round(tablespace_size/1024/1024,2) "tablespace_size(mb)", 
  round(used_percent) used_percent 
from dba_tablespace_usage_metrics 
order by used_percent desc;

 

[ 일반 테이블스페이스 조회 ]

-- 테이블스페이스 상태 조회 

col tablespace_name for a30
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces
  3  where rownum < 10;

 

TABLESPACE_NAME                STATUS             CONTENTS        EXTENT_MANAGEMENT    SEGMENT_SPAC
------------------------------ ------------------ --------------- -------------------- ------------
SYSTEM                         ONLINE             PERMANENT       LOCAL                MANUAL
SYSAUX                         ONLINE             PERMANENT       LOCAL                AUTO
UNDOTBS1                       ONLINE             UNDO            LOCAL                MANUAL
TEMP                           ONLINE             TEMPORARY       LOCAL                MANUAL
USERS                          ONLINE             PERMANENT       LOCAL                AUTO

-- 테이블스페이스 크기, 파일위치 조회

select tablespace_name, status, contents, extent_management, segment_space_management
from dba_tablespaces;

-- 테이블스페이스 크기, 파일위치 조회 (2) (1)과 동일한 결과 나옴.

SQL> select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files where rownum < 10;

-- 데이터파일의 실제 사용량 조회

set line 200
col file# for 999
col ts_name for a10
col total_blocks for 9999999
col used_blocks for 9999999
col pct_used for a10
select distinct d.file_id file#
  , d.tablespace_name ts_name
  , d.bytes/1024/1024 mb
  , d.bytes / 8192 total_blocks
  , sum(e.blocks) used_blocks
  , to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0)*100, '09.99') || ' %' pct_used
from dba_extents e, dba_data_files d
where d.file_id = e.file_id(+)
group by d.file_id, d.tablespace_name, d.bytes
order by 1,2;

 

FILE# TS_NAME MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
1 SYSTEM 2610 334080 290736  87.03 %
2 SYSAUX 3740 478720 455496  95.15 %
3 UNDOTBS1 30720 3932160 67088  01.71 %
4 UNDOTBS1 30720 3932160 65296  01.66 %
5 USERS 1057.5 135360 132488  97.88 %