[DB] Oracle Tablespace 정리

테이블스페이스(TableSpace) 정의

 

테이블이 저장되는 공간입니다.

오라클 에서는 테이블스페이스라고 해서 테이블이 저장될 공간을 먼저 만들고 나서

테이블을 생성합니다. 각각의 테이블을 테이블스페이스별로 나누어서 관리와 퍼포먼스의

향상을 가지고 오는것입니다.

 

테이블스페이스를 생성하면 정의된 용량만큼 미리 확보한 테이블스페이스가 생성되어

지고 생성되어진 테이블스페이스에 테이블의 데이타가 저장됩니다.

 

예를 들면 많은 데이터가 쌓일 게시판 테이블은 기본용량 100메가 자동확장 10메가로

테이블스페이스를 만들어서 그곳에 게시판 테이블을 만들어 쓰면 게시판 데이터는 그곳에

100메가까지 데이터가 저장되고 용량 초과시 자동적으로 10메가단위로 테이블 스페이스의

크기는 확장됩니다.

 

이렇게 각 테이블의 특성에 맞게 테이블스페이스를 만들어 활당하면 좋습니다. 물론 각

스키마별로 지정된 테이블스페이스를 만들어 써야 관리적측면에서 무척이나 효율적입니다.

 

MySQL 에서는 DB 시스템 관련사항(사용자 정보, DB정보)을 가지고 있는 것이 mysql DB

라면 Oracle 에서는 system 테이블스페이스로 이해하면 됩니다.

 

※ 테이블 스페이스를 만들지 않으면 ORACLE서버가 자동으로 만들어 줍니다.

일반적인 테이블스페이스 생성 과정

 

1. SYS 계정으로 접속해서 Mytablespace라는 테이블 스페이스를 만듭니다.

 

2. SYS 계정에서 SCOTT2 유저를 생성합니다.( default tablespace는 Mytablespace로 만듭니다)

 

3. SCOTT 계정의 emp 테이블을 scott2 계정으로 복사 합니다.

 

create table scott2.emp table space mytablespace as select * from scott.emp;

이렇게 하면 성공하게 됩니다.

 

테이블스페이스(TableSpace) 개념

 

테이블의 Data 를 Delete 하고 Commit 하여도 Tablespace의 사용량은 변하지 않음

 

테이블의 크기가 늘어나서 테이블 스페이스 크기가 자동으로 늘어나게 설정한 상태에서 테이블의 크기가 줄어들면 자동으로 테이블 스페이스의 크기가 줄어들지는 않습니다. 이때는 사용자가 수동으로 크기를 설정해야 합니다.

 

테이블 스페이스는 미리 공간을 잡아 놓았으므로 한번 늘어난 넘이 데이타가 줄었다고 해서 줄어들지 않습니다. 10명이서 들어가기 위해 방을 큰걸 잡았다가 10명이 살던 방에서 3~4명이 나갔다고 방이 줄어들진 않는다고 보시면 됩니다.

 

일반적으로 scott라는 유저가 생성되면 기본적 테이블스페이스와 템프테이블스페이스가

지정됨니다. 테이블을 만들면 기본적인 테이블 스페이스에 저장되고 그 테이블 스페이스는

(create tablespce 테이블 스페이스 datafile '경로/파일명') 이렇게 만들어진 경로와 파일에

내용이 저장되어 집니다.

 

현재 oracle db의 만들어진 테이블 스페이스 보기

 

system 으로 로그인 해서 select * from dba_tablespaces; 쿼리 실행

해당 계정으로 로그인해서 select distinct tablespace_name from user_tables; 쿼리 실행

 

※ TABS 는 USER_TABLES 의 동의어입니다. 두개는 같은 것입니다.

 

테이블스페이스에 포함된 테이블 알아보기

 

SELECT TABLESPACE_NAME, TABLE_NAME FROM USER_TABLES WHERE TABLESPACE_NAME = '테이블스페이스명';

-- 해당 테이블스페이스에 포함된 테이블 정보를 조회

 

현재 ORACLE서버에 만들어져있는 TABLESPACE보기(SYSTEM계정에서 실행)

 

SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;

 

FILE_NAME : datafile의 물리적인 위치와 파일명을 알 수 있습니다. 
TABLESPACE_NAME : 테이블 스페이스의 이름을 알 수 있습니다. 
BYTES : 테이블 스페이스의 크기를 알수 있습니다. 
STATUS : 테이블 스페이스의 이용 가능 여부를 알 수 있습니다.

 

현재 계정에서 사용할수 있는 테이블의 용량 조회

 

SELECT * FROM USER_SEGMENTS;

 

테이블 스페이스별 사용 가능한 공간의 확인

 

SELECT tablespace_name, SUM(bytes), MAX(bytes) 

FROM DBA_FREE_SPACE 

GROUP BY tablespace_name;

 

SUM을 사용한 이유는하나의 테이블 스페이스에 분산되어 있는 여유공간을 합한 것이며, 

MAX를 사용한 이유는 여유 공간중 가장 큰 공간의 SIZE를 의미 합니다.

 

테이블 스페이스 생성명령

 

CREATE TABLESPACE storm

DATAFILE 'C:\ORACLE\ORADATA\app_data.dbf' SIZE 100M

DEFAULT STORAGE 
(

  INITIAL    10K
  NEXT      10K
  MINEXTENTS 2
  MAXEXTENTS 50
  PCTINCREASE 50

)

 

※ 추후에 위의 데이타크기(100M) 보다 커지면 데이타크기는 수동명령으로 늘릴수 있다.

 

※ 만들때 SIZE 100M 뒤에 autoextend on next 10m maxsize 500 옵션을 주면 용량이 FULL로 차면 자동으로 용량이 증가 됩니다.

 

※ 비슷한 성격(연관성)의 테이블을은 같은 테이블스페이스에 두는게 좋다.

 

※ 일반적으로 테이블 스페이스 생성시 SIZE를 안주면 디폴트로 2GB 로 잡힙니다.

 

생성명령 설명

CREATE USER TEST3

IDENTIFIED BY TEST3

DEFAULT TABLESPACE ABC

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON ABC;

설명

test3계정은 기본테이블스페이스는 abc를 사용하고

템프(소트용)테이블스페이스는 temp를 쓰고

abc테이블스페이스를 제한없이 사용가능하다. 

 

-------------------------------------------------------------------------------------

 

※ SCOTT 계정의 테이블은 SYSTEM 테이블스페이스에 저장됩니다.

※ SYSTEM TABLESPACE 에는 동적 성능 뷰나 기타 시스템 관련 중요한 테이블이 저장됩니다.

※ SYSTEM TABLESPACE 가 FULL이 되면 Oracle 서버가 다운된다.

 

테이블스페이스 수정 명령

 

ALTER DATABASE DATAFILE '데이터파일경로' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- 해당 데이터파일경로에 해당하는 테이블스페이스의 크기가 FULL이 되면 자동으로 100메가씩 늘어난다.

 

또는

 

ALTER TABLESPACE 테이블스페이스명 ADD DATAFILE '추가할데이터파일명' SIZE 1024M;  
-- 해당 테이블스테이스의 용량을 1024메가로 변경한다.

 

** 테이블 스페이스를 설정 안하면 디폴트로 잡히나요?

테이블 스페이스 를 설정 하지않고 테이블을 만들면 무한대로 데이타가 들어가나요?

스키마 생성 유저의 default tablespace에 생성이 되고, 해당 디폴트 테이블 스페이스가 수용

가능한 영역까지 데이타가 들어갑니다.

 

특정계정의 테이블스페이스 생성시

 

SYSTEM계정에서 생성하는게 맞습니다. 혹은 생성 권한을 가지고 있는 계정에서 생성하시면 됩니다. "특정계정의 테이블스페이스인건 어떻게 아는건지" 라는 질문에 대한 답. 
테이블스페이스는 특정 계정에 속하지 않습니다. 
단지 계정을 만드실때 디폴트 테이블 스페이스를 지정하게 되는데 아마 그것 때문에 테이블스페이스가 특정 계정에 속해 있다고 생각하신 모양입니다.

테이블 스페이스가 왜 필요하고 어떻게 사용되는 건지는 강좌를 통해서 확인 하시기 바랍니다.

 

일단 오라클은 유저를 생성하면서 디폴트테이블스페이스를 지정합니다.

테이블을 만들게 되면 그 테이블 스페이스에 들어 가게되고 그테이블 스페이스는

테이블 스페이스 생성때 설정하는 경로에 위치하게되는 겁니다.

 

select * from all_tables(권한전체) 
에서 테이블의 정보를 볼수 있습니다 
user_tables / dba_tables 도 있는데 
사용자 db전체

이 테이블에 정보를 보고 알아서 저장해줍니다.