[Oracle] Tablespace 개념 및 생성/추가/Resize/용량 확인

1. Tablespace란?

• 데이터 블록(저장하는 가장 최소의 논리적 단위) >> 익스텐트 >> 세그먼트 >> 테이블 스페이스
• 하나의 테이블스페이스느 최소 1개의 데이터 파일로 구성됨
• 데이터는 파일에 저장이 되는데, 파일은 물리적 단위
• smallfile : 데이터 파일 사이즈가 OS한계로 인해서 32G까지가 한계(오라클에서 unix, linux, windows 동일)이며 파일을 최대 1,000개까지 붙일 수 있음
• bigfile : TB까지 확장가능하고 1개만 가능

 

---

 

 

2. Tablespace 생성

create tablespace 테이블스페이스명 datafile '/경로/테이블스페이스파일명.dbf'
size 초기용량(100m,1g 등) autoextend [on next 자동증가 용량 / off];

 

 

---

 

 

3. Tablespace 용량 확인

select *
from (
    SELECT A.TABLESPACE_NAME                                       AS TBS_NAME,
           ROUND(SUM(A.BYTES)/1024./1024.)                                  AS TOT_MB,
           round(SUM(A.BYTES)/1024/1024 - NVL(SUM(SZ_MB),0))                   AS USE_MB,
           ROUND(NVL(SUM(SZ_MB),0))                                       AS FREE_MB,
           ROUND(NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/1024./1024.) ) * 100,100)) AS "USAGE%"
      FROM DBA_DATA_FILES A,
           ( SELECT /* parallel(8) */ FILE_ID  ,
                    round(SUM(BYTES)/1024/1024,2) AS SZ_MB
               FROM DBA_FREE_SPACE
              GROUP BY FILE_ID
           ) B
 WHERE A.FILE_ID = B.FILE_ID (+)
 GROUP BY A.TABLESPACE_NAME
 union
 select dtf.tablespace_name,
       dtf.TOT_SIZE "TOT_SIZE(MB)",
       nvl(ss.USED_SPACE_SORT_SEGMENT,0) "TOTAL_USED(MB)",
       TOT_SIZE - nvl(ss.USED_SPACE_SORT_SEGMENT,0) "TOTAL_FREE(MB)",
       ROUND(nvl(ss.USED_SPACE_SORT_SEGMENT,0)*100/(dtf.TOT_SIZE)) "USAGE%"
from
    ( select /* RULE */ tablespace_name,
             round(sum(bytes)/1024/1024) TOT_SIZE from dba_temp_files
      group by tablespace_name ) dtf,
    ( select tablespace_name,
             round(sum(USED_BLOCKS)*( select distinct value from Gv$parameter where name = 'db_block_size' )/1024/1024) USED_SPACE_SORT_SEGMENT
     from Gv$sort_segment ss
     group by tablespace_name ) ss
    where ss.tablespace_name(+)  = dtf.tablespace_name
     )
 order by 5 desc

 

 

 

---

 

 

4. Tablespace 추가

  4-1. Tablespace 용량 확인

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = '테이블스페이스명'
 GROUP BY TABLESPACE_NAME;

 

 

  4-2. Diskgroup 용량 확인

• Tablespace가 추가될 Diskgroup에 용량이 부족하다면, 추가 불가( Diskgroup 용량을 높여야함)

select group_number "Group#",
       name,
       total_mb/1024 TOTAL_GB,
       round((total_mb - USABLE_FILE_MB)/1024,2) USED_GB,
       USABLE_FILE_MB/1024 USABLE_FILE_GB,    
       free_mb/1024 FREE_GB,
       100-round(free_mb/total_mb*100) "usgae(%)",
       ((FREE_MB - REQUIRED_MIRROR_FREE_MB))/1024 USABLE_CALC_GB,
       type, state
from v$asm_diskgroup;

 

 

  4-3. Tablespace의 Datafile 확인

SELECT * FROM DBA_DATA_FILES;  -- 기존에 데이터들 확인
SELECT * FROM V$ASM_DISKGROUP; -- 여기서 확인도 가능

 

  4-4. Tablespace 추가

Alter TABLESPACE 테이블스페이스명 add DATAFILE 'Datafile명' SIZE 30G;

  4-5. 기타 알아두면 좋은 것

• V$ASM_DISKGROUP: 파일시스템 MOUNT된 포인트(Tablespace)
• V$ASM_DISK : DISK그룹 구성하는 파일들(DATAFILE)

 

---

 

 

5. Tablespace Resize

  5-1. 데이터파일 및 사용량 확인하기

• resize는 데이터파일의 크기를 줄이는 것임

-- Datafile 확인
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'Tablespace명';

-- Datafile 사용량 확인
SELECT BLOCK_ID, BLOCKS FROM DBA_EXTENTS WHERE FILE_ID = 'Datafile명';

 

 

  5-2. Resize 진행하기

• 현재 사용중인 데이터파일 크기보다 크면 resize 가능

 

ALTER DATABASE DATAFILE 'DATAFILE경로/Datafile명' RESIZE 30G;

 

 

---

 

 

6. Tablespace autoexend 설정 변경

  6-1. autoextend 설정 확인

SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES;

 

 

  6-2. autoextend 설정 변경

• autoextend = ON 인 경우 maxsize 지정 필요(OFF인 경우 SIZE 30G로 지정)
• 보통 운영에서는 Tablespace 예측이 필요해 보통 autoextend를 off (보통 off인 상태에서 90% 넘으면 추가필요)

ALTER DATABASE DATAFILE 'Datafile경로/Datafile명' AUTOEXTNED [ON/OFF] MAXSIZE 30G;