오라클과 MSSQL의 차이 차이점

오라클은 데이터를 관리할 때 데이터들이 데이터파일에 들어있다. 또한 메모리는 구역으로 나누어 관리한다. 서버프로세스가 관련 데이터를 메모리에서 찾을 때 뒤죽박죽 되어있다면 찾는데만도 시간이 오래 걸리기 때문이다.

하나의 예를 들어보자.
봄, 여름, 가을, 겨울옷이 각 250벌씩 총 1000벌을 가지고 있다고 하자. 옷장에 1000벌의 옷을 그대로 넣으면 뒤죽박죽 되어있어 나중에 여름옷만을 꺼내 입기에 매우 불편할 것이다.

그래서 옷장을 4개의 구역으로 나누었다. 봄, 여름, 가을, 겨울 이렇게 4개의 구역으로 나누고 각 구역별로 옷을 넣는다. 그러면 나중에 가을옷만 세탁하려고 꺼낼 때 매우 편할 것이다.

이처럼 데이터를 메모리로 끌어올릴 때 무조건 놓는 것이 아니라 관련되어 있는 것들끼리 놓는다. 이처럼 메모리상에 나뉘어진 공간을 테이블스페이스라고 부른다.

---

개념 이해를 위해 또 하나의 예를 들어보자.
백화점에 가면 각 제품제조사 별로 매장이 나뉘어 있다. 명품코너 매장이 있는 층으로 갔다고 하면 구찌, 루이비통, 티파니, 불가리 등..
여기서 내가 불가리 제품만 사려고 하면 다른 데 갈 필요없이 불가리 매장만 가면 된다. 즉, 찾기도 쉽고 관리하기도 쉽다.

오라클은 메모리를 관리할 때 구역을 나누어 관리하는데, 그 구역(여기서는 매장)을 테이블스페이스라고 한다. DBA는 그 매장을 관리하는 사람이다.

테이블스페이스를 만들면 그 안에는 데이터파일이 들어간다.
매장마다 창고가 있는데 이때 창고에 해당하는 것이 데이터파일이다.
모든 물건을 매장에 다 갖다놓을 수는 없기 때문에 매장마다 창고를 두어야 하고, 최소한 매장당 한개 이상이 있어야 한다.

데이터베이스는 건물이다. 하나의 건물 안에 여러 매장이 들어서 있다.
신규 프로젝트가 진행되면 테이블스페이스를 새로 하나 만드는데 MSSQL은 데이터베이스를 새로 하나 만든다. 개념의 차이일 뿐 비슷하다.

---

◆ 테이블스페이스와 데이터파일
오라클은 데이터를 논리적으로 테이블스페이스 안에, 물리적으로 데이터파일 안에 저장한다.
  ◇ 테이블스페이스
    - 하나 이상의 데이터파일로 구성되어 있다.
  ◇ 데이터파일
    - 하나의 데이터파일은 하나의 테이블스페이스에 속해야 한다.


◆ 테이블스페이스의 종류
  ◇ System 테이블스페이스
    - 데이터베이스 생성 시 같이 생성된다.
    - 데이터딕셔너리를 담고 있다.(시스템 테이블스페이스)
    - 언두 세그먼트를 담고 있다.(언두 테이블스페이스)
  ◇ Non-System 테이블스페이스
    - 사용자가 필요시 만들고 사용할 수 있는 테이블스페이스


◆ 테이블스페이스 생성

SQL> create tablespace 테이블스페이스명
   datafile '데이터파일명' size 크기;

---

위 명령문은 테이블스페이스를 생성하는 명령인데, 빨간색 네모로 친 부분에 대한 설명이 그림에 써있다.
저 명령문을 실행하면 userdata라는 이름의 테이블스페이스가 메모리의 한곳에 생성이 되고, 100M용량의 userdata01.dbf라는 이름의 데이터파일을 OS에게서 할당받는다. 100M가 다 차면 자동으로 5M씩 늘려주고 최대로 늘어날 수 있는 용량은 200M이다. 200M까지 꽉 차버리면 에러가 난다.
(위에서 100M용량의 데이터파일을 OS에게서 할당받았는데, OS명령어로는 쓰고 있던 안쓰고 있던 아무리 조회해봐야 100M이다. 오라클 명령어로 조회해야 현재 해당 데이터파일이 얼만큼 썼는지 조회 가능하다.)

데이터파일 용량 관련해서 에러가 나면 데이터파일을 추가해주던지 기존의 데이터파일 용량을 확장시켜 주면 된다.
DBA들은 수시로 용량을 검사해서 늘리던지 추가해주던지 하는 작업을 해야 한다. 이것이 DBA의 기본업무 중 하나이다.

데이터파일이 가득찼을 때 위 명령처럼 자동으로 늘어나게끔 할 수도 있고, DBA가 수동으로 명령을 날려서 늘리는 방법도 있다.

만약에 autoextend on; 하고 뒤에 아무 옵션도 주지 않으면 어떻게 될까? 이 경우에는 데이터파일 하나가 32비트 OS에서는 16G, 64비트 OS에서는 32G까지 최대로 늘어난다. 하지만 이는 바람직하지 못하다. DBA가 조금 귀찮다고 해서 아무런 옵션도 안주고 계속 늘어나게끔 하면 나중에 데이터파일 용량이 어마어마하게 커서 백업을 못받는 사태가 발생할 수 있다.

어지간하면 autoextend on 옵션 뒤에 maxsize로 최대용량을 생각해서 지정해주는 것이 좋다.

---

◆ 테이블스페이스 관리 방법 두가지
  1. DMT(Dictionary Managed Tablespace)
    - Free extent 관리를 데이터딕셔너리에서 관리
  2. LMT(Locally Managed Tablespace)
    - Free extent 관리를 테이블스페이스에서 관리
    - 비트맵으로 정보 관리

◇ DMT
오라클 백그라운드 프로세스 중 DBWR은 DB캐시에서 데이터파일로 변경된 데이터들을 내려쓰는 역할을 한다. 내려 쓸려고 하는데 데이터파일에 빈 블록이 너무 많아서 어디 블록에 내려써야 할지 모르겠다. 그래서 DBWR은 "저 어디에 내려써야 하죠?"라고 누구에게 물어본다. 누구에게 물어볼까? 정답은 딕셔너리이다.
딕셔너리는 해당 데이터파일에 현재 사용되어진 블록과 사용해도 되는 블록들의 정보를 가지고 있다. 이렇게 딕셔너리가 블록들을 관리하는 것을 DMT라고 한다.
그러나 이 경우 데이터의 양이 많으면 문제가 될 수 있다.

EXTENT MANAGEMENT DICTIONARY

◇ LMT
위의 것를 이어서 학원의 예를 들어보자.
안내 데스크에는 학원의 모든 과목별 자리배치도를 가지고 있다. 이때 이 안내를 딕셔너리라고 한다.
문제는 개강날에 터진 것이다. 저글링이 떼로 몰려오는 것처럼 학생들이 서로 자기들의 자리배치를 물어보면 안내데스크는 얼마 못가 혼란 상태가 되어버릴 것이다.
이 문제를 해결하기 위해 안내데스크에서는 해당 강의장만 알려주고, 좌석배치는 학생들이 직접 해당 강의장의 정문에 붙여있는 배치도를 보게끔 하는 것이다. 
학생이 적을 때는 이 방법이 더 늦지만, 학생이 많을 때는 이 방법이 효율적으로 빠르다. 이렇게 딕셔너리는 해당 데이터파일의 위치만을 알려주고, 실제 어디 블록에 써야 하는지는 해당 데이터파일의 헤더에 정보를 저장해두는 방법을 LMT라고 한다.

EXTENT MANAGEMENT LOCAL     // 8i까지는 명시. 9i부터는 기본값.

① A를 어디 데이터파일에 내려써야 하는지 딕셔너리에 물어본다.
② 해당 데이터파일에서 어느 블록위치에 내려써야 하는지 헤더 정보를 확인한다.(헤더에는 0과 1의 정보로 블록들의 상태정보가 저장되어 있다.)
③ DBWR이 A를 내려쓰기 위해 준비한다.
④ 해당 블록 위치에 내려쓴다.

오라클 8버전까지는 DMT만 있었는데, 8i부터는 LMT라는 기술이 도입되어 LMT와 DMT가 공존했다. 디폴트는 DMT였는데, 9i부터는 테이블스페이스를 생성하면 무조건 LMT로 만들어진다.


◆ 언두 테이블스페이스
  - 언두 데이터들만 모아놓은 테이블.
  - 다른 테이블들을 저장할 수 없다.

SQL> create undo tablespace 테이블스페이스명
   datafile '데이터파일명' size 크기;

◆ 읽기 전용(Read Only) 테이블스페이스
  - select만 가능
  - DML은 불가능
  - drop은 가능

SQL> alter tablespace 테이블스페이스명 read only;

◆ 테이블스페이스 온라인 & 오프라인
  - 시스템 테이블스페이스, 언두 테이블스페이스, 디폴트 Temporary 테이블스페이스는 오프라인 불가능
  - 시스템 테이블스페이스가 오프라인 된다면 딕셔너리에 있는 정보를 쓰지 못함.
  - 언두 테이블스페이스가 오프라인 된다면 DML작업을 할 수가 없음.

SQL> alter tablespace 테이블스페이스명 offline [immediate];
SQL> alter tablespace 테이블스페이스명 online;

IMMEDIATE : 누군가가 해당 테이블스페이스에 있는 테이블을 사용하고 있어 오프라인을 할 수 없을 때 강제로 오프라인시킴.


◆ 데이터파일 온라인 & 오프라인

SQL> alter database datafile '데이터파일명' offline;
SQL> alter database datafile '데이터파일명' online;

◆ 데이터파일 이동
DB가 꺼져있는 상태에서는 모든 파일을 다 옮길 수 있다. 단, DB가 오픈상태에서는 시스템, 언두, 리두로그는 옮기지 못한다.

<DB 오픈상태>
SQL> alter tablespace 테이블스페이스명 rename
   datafile '이전경로'
   to '새경로';

<DB 마운트상태>
SQL> alter database rename
   file '이전경로'
   to '새경로';

 

<데이터파일 이동 순서>
1. 테이블스페이스 오프라인
2. OS명령어를 이용하여 해당 데이터파일을 복사함.
3. alter 명령어를 이용하여 컨트롤파일과 딕셔너리에 데이터파일 위치정보 변경사항 반영.
4. 테이블스페이스 온라인

순서가 바뀌면 에러난다. 데이터파일을 이동시키지 않고 alter명령어를 날리면 서버프로세스가 해당 위치에 파일이 있는지 컨트롤파일에서 보고 가봤는데 파일이 없으니 에러를 띄운다. 순서를 주의하자.

위 그림의 예처럼 국민은행 테이블스페이스를 다른 디스크로 옮기는데 만약 DB를 내리고 작업한다면 우리은행 같은다른 테이블스페이스 또한 쓰지 못하므로, 국민은행 테이블스페이스만 오프라인 시켜놓고 작업하면 된다.


◆ 테이블스페이스 삭제

SQL> drop tablespace 테이블스페이스명
   including contents
   and datafiles
   cascade constraints;

DROP TABLESPACE만 하면 그 안에 만약 테이블들이 들어있다면 안지워짐.
INCLUDING CONTENTS : 안에 테이블이 있건 없건 관계없이 무조건 삭제.
AND DATAFILES : 데이터파일도 같이 삭제.
CASCADE CONSTRAINTS : 물려있던 제약조건까지 삭제됨.


◆ 테이블스페이스 리사이즈(Resize)
  ◇ 자동 증가(autoextend)

SQL> create tablespace 테이블스페이스명
   datafile '데이터파일명' size 크기
   autoextend on next 증가크기 maxsize 최대크기;

SQL> alter database
   datafile '데이터파일명'
   autoextend on next 증가크기 maxsize 최대크기;

  ◇ 수동 증가(alter 명령어)

SQL> alter database
   datafile '데이터파일명'
   resize 크기;

SQL> create tablespace test
   datafile '/home/oracle/oradata/testdb/test01.dbf' size 256K;

SQL> create table scott.abc (no number, name varchar2(20)) tablespace test;

SQL> begin
   for i in 1..1000 loop
      insert into scott.abc values (1, dbms_random.string('A', 10));
   end loop;
   end;
   /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /

SQL> select count(*) from scott.abc;

SQL> insert into scott.abc
   select * from scott.abc;
ORA-01653 에러 발생함. 테이블스페이스 공간 부족 현상.

SQL> alter database datafile '/home/oracle/oradata/testdb/test01.dbf' autoextend on;

SQL> insert into scott.abc
   select * from scott.abc;

SQL> select count(*) from scott.abc;

◆ 테이블스페이스에 데이터파일 추가
리두로그는 같은 그룹에 속한 멤버들끼리는 용량이 동일하나, 같은 테이블스페이스에 속한 데이터파일들끼리는 용량이 서로 달라도 상관없다.

SQL> alter tablespace 테이블스페이스명
   add datafile '데이터파일명'
   size 크기;

◆ 테이블스페이스에서 데이터파일 삭제
단, 이 방법은 해당 데이터파일에 데이터가 아무것도 들어있지 않을 때 적용됨. 데이터가 들어가있으면 지워지지 않는다. 한 테이블스페이스에 데이터파일을 여러 개 만들어놓으면 리두로그처럼 하나가 다 차면 다음 것을 사용하는 것이 아니라 이거 조금쓰고 저거 조금쓰고 하는 형식이다. 그래서 이 방법은 데이터파일을 잘못 추가했을 때 바로 삭제하는 데 쓰인다.

SQL> alter tablespace 테이블스페이스명
   drop datafile '데이터파일명';

◆ 테이블스페이스 관련 딕셔너리와 동적성능뷰

● 테이블스페이스
  - DBA_TABLESPACES
  - V$TABLESPACE
● 데이터파일 정보
  - DBA_DATA_FILES
  - V$DATAFILE
● Temp파일 정보
  - DBA_TEMP_FILES
  - V$TEMPFILE

---

[문제] /home/oracle/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
                               disk2/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
                               disk3/control03.ctl, system
                               disk4/sysaux, undo
                               disk5/users, example, insa
          15분 안에 위 경로대로 옮겨보세요.

[문제] /home/oracle/disk1/system, sysaux
                               disk2/undo, users, example, insa
                               disk3/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
                               disk4/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
                               disk5/control03.ctl, redo01_c.log, redo02_c.log, redo03_c.log
          15분 안에 위 경로대로 옮겨보세요.

[문제] 디스크 10G를 하나 더 추가한 후
          /data2 에 마운트 후
          oracle 계정이 쓸 수 있게 권한을 준 후 
          15분 안에 아래의 경로대로 옮겨보세요.
          /data/disk1/system, sysaux
                   disk2/undo, control03.ctl
                   disk3/users, example, insa
          /data2/disk4/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
                    disk5/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log