SQL 구문별 상세 설명 - DDL

DDL(Data Definition Language)은 데이터베이스의 구조나 스키마를 가진 오브젝트를 다루는(생성, 삭제, 변경 등) 데이터베이스 언어이다. 오라클에서 이용하는 DDL의 종류는 데이터베이스 버젼별로 차이가 있지만, DDL 문자 수만 60개가 넘는다. 자주 이용하는 오라클의 주요 오브젝트(데이터베이스, 데이터파일, 테이블 스페이스, 테이블, 뷰)를 ERD로 표현하면 다음과 같다.

* 테이블 스페이스 관리

데이터베이스에서 테이블 스페이스(Tablespace)는 각종 오브젝트(테이블, 뷰, 인덱스, 시퀀스)를 저장하는 공간이다. 앞의 데이터베이스 ERD에서 설명했듯이 하나의 테이블 스페이스는 한개나 그 이상의 데이터파일로 구성된다.

■ 테이블 스페이스의 특징

- 공간관리, 데이터의 사용 가능 관리, I/O 성능 개선, 경합 감소, 부분 백업 등에 사용

- 하나 이상의 파일로 구성

- DB 실행 동안 온라인이될 수 있다.

- 시스템 테이블 스페이스나 활동 롤백 세그먼트를 갖는 테이블 스페이스를 제외하고 오프라인될 수 있다.

- 읽기-쓰기 또는 읽기-전용으로 변경할 수 있다.

- 데이터베이스는 최소 하나의 테이블 스페이스로 구성(system)

CREATE TABLESPACE tablespace
         DATAFILE filepaec [, filespec] ...
         [AUTOEXTEND filespec [filespec] [ON | OFF]]
         [NEXT integer [K | M]]
         [MAXSIZE [UNLIMITED | integer [K | M]]
         [DEFAULT STORAGE storage_clause]
         [ONLINE | OFFLINE

- tablespace : 생성하고자 하는 테이블 스페이스를 기술한다.
- filespect : 시스템 디렉토리에 만들고자 하는 데이터파일명을 기술한다. 데이터파일은 여러 개를 기술할 수 있다.
- AUTOEXTEND : 해당 데이터파일의 용량이 모두 사용되면 데이터파일의 크기를 자동으로 증기시킨다.
- STORAGE : 테이블 스페이스의 저장 옵셥을 지정한다.
- ONLINE/OFFLINE : 테이블 스페이스를 사용할 것인지 사용하지 않을 것인지 지정한다.

(1) 데이터파일 및 테이블 스페이스 생성

[예제] 500M의 데이터파일을 가진 데이터파일을 '/ORA8/ORADATA/DB1/DF_TEST01.DBF'에 생성하고 데이터파일을 이용하여 테이블 스페이스 TS_TEST01을 생성하라(단 INITIAL:50K,NEXT:50K,MINEXTENTS:10, MAXEXTENTS:50,

PCTINCREASE: 0),

CREATE TABLESPACE TS_TEST01
        DATAFILE '/ORA8/ORADATA/DB1/DF_TEST01.DBF SIZE 500M
        DEFAULT STORAGE {
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 10
        MAXEXTENTS 50
        PCTINCREASE 0);

(2) 테이블 스페이스에서 데이터파일의 이용 현황을 조회하는 방법

생성된 테이블 스페이스와 데이터파일은 DBA_DATA_FILES 데이터 사전에서 조회할 수 있다.

SELECT TABLESPACE_NAME, FILE_NAME, BYTES
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_NAME

■ 테이블 스페이스의 변경

ALTER TABLESPACE tablespace
        DATAFILE filespec [, filespec] ...
        [AUTOEXTEND filespec [filespec] [ON | OFF]
        [NEXT integer [K | M]
        [MAXSIZE [UNLIMITED | integer [K | M]]
        [DEFAULT STORAGE storage_clause]
        [ONLINE | OFFLINE]

- tablespace: 변경ㄷ하고자 하는 테이블 스페이스명을 기술한다.
- filespec : 시스템 디렉토리에 만들고자 하는 데이터파일명을 기술한다. 데이터파일은 여러 개를 기술할 수 있다.
- filename: 이미 존재하는 데이터파일의 이름을 다른 이름으로 변경한다.

(3) 테이블 스페이스에서 데이터파일 변경

[예제] 위에서 생성한 테이블 스페이스에 '/ORA8/ORADATA/DB1/DF_TEST2.DBF' 100M를 추가하라( 단 INITIAL : 10K, MINEXTENTS: 5, MAXEXTENTS: 5, POCTINCREASE: 0)

ALTER TABLESPACE TS_TEST1
ADD DATAFILE '/ORA8/ORADATA/DB1/DF_TEST2.DBF' SIZE 100M
 DEFAULT STORAGE (INITIAL 10K
                         NEXT 10K
                         MINEXTENTS 5
                         MAXEXTENTS 5
                         PCTINCREASE 0);

[예제] 위에서 생성한 데이터파일 '/ORA8/ORADATA/DB1/DF_TEST01.DBF' 크기를 1G로 변경하라.

ALTER TABLESPACE TS_TEST1 RESIZE DATAFILE '/ORA8/ORADATA/DB1/DF_TEST02.DBF' SIZE 1G

■ 테이블 스페이스의 삭제

테이블 스페이스를 한번 삭제하면 그 안에 있는 내용을 복구 할 수 없다. 반드시 삭제가 필요한 경우인지 확인하고 삭제하여 만일을 위해서 데이터는 백업을 받고 삭제하도록 한다.

DROP TABLESPACE tablespace
  [INCLUDNG CONTENTS [ CASCADE CONSTRAINTS]]
- tablespace : 삭제하고자 하는 테이블 스페이스명을 기술한다.
- INCLUDING CONTENTS : 테이블 스페이스 내에 존재하는 모든 내용(테이블, 인덱스)등 같이 삭제된다.
- CASCADE CONSTRAINTS: 다른 테이블 스페이스와 연결된 참조 무결성도 같이 삭제된다.

테이블 스페이스가 삭제된다고 하여 데이터파일이 삭제되는 것이 아니다. 데이터파일을 삭제하기 위해서는 데이터파일이 존재하는 디렉토리에서 해당 데이터파일을 삭제하면된다. 하지만 데이터파일을 삭제하기 전에 DBA_DATA_FILES라는 데이터 사전에서 데이터파일이 사용중인지 조회한 후 존재하지 않으면 삭제하도록 한다. 만약 계속 사용중인 데이터파일을 삭제한다면 데이터베이스가 정상적으로 운영된다 하더라도 삭제된 데이터파일에서 데이터를 검색하면 에러가 발생한다. 또한 데이터베이스를 종료시켰다가 다시 기동하면 데이터파일 깨짐 에러가 발생한다.'

(1) 테이블 스페이스와 데이터파일 삭제

[예제] TS_TEST01과 테이블 스페이스와 데이터파일 '/ORA8/ORADATA/DB1/DF_TEST01.DBF'을 삭제하라

① DROP TABLESPACE TS_TEST01;
② /ORA8/ORADATA/DB1 디렉토리에서 DF_TEST01.DBF 파일 삭제