DDL 명령과 딕셔너리

* DDL (Data Definition Language) : CREATE(생성), ATTER(수정), TRUNCATE(잘라내기), DROP(삭제)
* DML (Data Manipulation Language) : INSERT(입력), UPDATE(변경), DELETE(삭제), MERGE(병합)
* DCL (Data Control Language) : GRANT(권한 주기), REVOKE(권한 뺏기)
* TCL (Transaction Control Language) : COMMIT(확정), ROLLBACK(취소)
* SELECT : 어떤 분류에서는 DQL (Data Query Language)이라고 하기도 합니다.

DDL

 : 오브젝트를 생성하고, 변경하고, 관리하는 명령어

 

CREATE

 : 새로운 오브젝트나 스키마를 생성할 때 사용하는 명령어

 

일반 테이블 생성

CREATE TABLE new_table(		// new_table이라는 이름으로 테이블을 생성1
    No NUMBER(3),		// No 컬럼은 숫자데이터만 들어갈 수 있으며 최대 길이는 3자리
    Name VARCHAR2(10),		// Name 컬럼은 가변형 문자가 들어갈 수 있으며 최대 길이는 10자리
    Birth DATE			// Birth 컬럼은 날짜 데이터만
);

 

기본 입력 값을 설정하면서 생성하기

   - DEFAULT는 사용자가 값을 입력하지 않을 경우 DAFUALT 뒤에 적힌 값을 자동으로 넣으라는 의미

 

한글 이름으로 테이블 생성하기

   - 실제 사용할 때 테이블명이나 컬럼명이 한글일 경우 불편함이 있음 (오류발생의 원일일 수도 있음)

 

Global Temporary Table(임시테이블) 생성

   - 오라클 8.1버전 부터드 등장한 기능

   - 데이터베이스에 저장할 목적이 아닌 임시 작업용 데이터를 저장하기 위해 만들어짐

   - 테이블을 생성하면 그 정의만의 딕셔너리에 저장되어 있다가 사용자가 해당 테이블에 엑세스하면

     메모리상에 해당 테이블을 만들고 데이터를 가져옴

   - 테이블의 데이터는 영구적이지 않아서 임시로 사용되다가 사라짐

     (사라지는 시점은 트랜잭션이 끝나거나 세션이 끝나는 시점)

   - 저장이 목적이 아닌 테스트가 목적인 작업에 사용

   - 기본적으로 세션별로 만들어지기 때문에 다른 세션에서 테이블을 공유할 수 없음

CREATE CLOBAL TEMPORARY TABLE 테이블명(
	컬럼1 데이터타입,
    컬럼2 데이터타입, ...,
	ON COMMIT [delete | preserve] ROWS;    	// Default 값은 delete
);

 : 터미널을 2개 열어서 한쪽에서 생성 후 다른 쪽에서 조회

   - 같은 계정이라도 다른 창에서는 다른 사람이 어떤 작업을 하는지 알 수 없다.

  - ON COMMIT delete ROWS를 설정해 놓았기 때문에 COMMIT을 하는 순간 데이터가 지워진다.

 

생성되어 있는 Temporary Table 조회

 

테이블 복사하기(CTAS라고도 부름)

 : 새로운 테이블을 생성할 때 기존에 만들어져 있는 테이블을 참조하여 생성하는 방법. 즉, 테이블을 복사하는 의미

(dept2 테이블)

CREATE TABLE DEPT2 (
 DCODE   VARCHAR2(06)  PRIMARY KEY,
 DNAME   VARCHAR2(30) NOT NULL,
 PDEPT VARCHAR2(06) ,
 AREA        VARCHAR2(30)
);

INSERT INTO DEPT2 VALUES ('0001','President','','Pohang Main Office');
INSERT INTO DEPT2 VALUES ('1000','Management Support Team','0001','Seoul Branch Office');
INSERT INTO DEPT2 VALUES ('1001','Financial Management Team','1000','Seoul Branch Office');
INSERT INTO DEPT2 VALUES ('1002','General affairs','1000','Seoul Branch Office');
INSERT INTO DEPT2 VALUES ('1003','Engineering division','0001','Pohang Main Office');
INSERT INTO DEPT2 VALUES ('1004','H/W Support Team','1003','Daejeon Branch Office');
INSERT INTO DEPT2 VALUES ('1005','S/W Support Team','1003','Kyunggi Branch Office');
INSERT INTO DEPT2 VALUES ('1006','Business Department','0001','Pohang Main Office');
INSERT INTO DEPT2 VALUES ('1007','Business Planning Team','1006','Pohang Main Office');
INSERT INTO DEPT2 VALUES ('1008','Sales1 Team','1007','Busan Branch Office');
INSERT INTO DEPT2 VALUES ('1009','Sales2 Team','1007','Kyunggi Branch Office');
INSERT INTO DEPT2 VALUES ('1010','Sales3 Team','1007','Seoul Branch Office');
INSERT INTO DEPT2 VALUES ('1011','Sales4 Team','1007','Ulsan Branch Office');

 : 모든 컬럼 다 복사하기/특정 컬럼만 복사하기

 : 테이블의 구조(컬럼)만 가져오고 데이터는 가져오지 않기

 

 

ALTER

 : 만들어져 있는 오브젝트를 변경하는 명령어

 : 테이블같은 경우에는 컬럼을 추가하거나 컬럼을 삭제하거나 컬럼명이나 테이블명을 바꾸는 등의 작업을 할 수 있음

 : 이 명령어는 부하가 많이 걸리는 명령어이므로 사용량이 많은 시간에 수행하는 것은 아주 위험하니 조심해야함

 

새로운 컬럼 추가하기

ALTER TABLE 테이블명 ADD(컬럼명 데이터타입);

 : 추가할 때 기본값 설정 가능

 

테이블의 컬럼 이름 변경하기

 : 컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 바꿀명;

 : 테이블명 변경

RENAME 테이블명 TO 바꿀명;

 

컬럼의 데이터 크기 변경

ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입(크기));

 : 최소 크기는 들어있는 데이터 값까지

 : 위에 LOC처럼 컬럼의 데이터 타입도 변경 가능하지만, 해당 컬럼이 데이터를 가지고 있지 않아야 함.

   (즉, 데이터 타입 변경은 사용할 일이 거의 없음)

 

컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 : 참조키로 설정되어 있는 부모 테이블의 컬럼의 삭제할 때

ALTER TABLE 테이블명 DROP COLUMN 컬럼명 CASCADE CONSTRAINTS;

 

 

TRUNCATE

 : 테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납하는 명령어

 : 해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아님

 : 해당 테이블에 생성되어 있던 인덱스의 내용도 함께 TRUNCATE됨

TRUNCATE TABLE 테이블명;

 

DROP

 : 테이블 자체를 삭제하는 명령어

 : 이 명령어를 수행하면 테이블 자체가 지워지며, 해당 테이블에 생성되어 있던 인덱스 또한 함께 삭제됨

DROP TABLE 테이블명;

 

DELETE, TRUNCATE, DROP 명령어의 차이점

 : DELETE - 데이터만 지워지고 쓰고 있던 디스크상의 공간은 그대로 가지고 있음

               테이블 용량은 줄어들지 않음

               삭제를 원하는 데이터만 골라서 삭제 가능

 

 : TRUNCATE - 최초에 테이블이 만들어졌던 상태, 모든 데이터를 삭제하고 컬럼 값만 남겨 놈

                    테이블 용량 줄어듬

                    인덱스 등도 TRUNCATE 됨

                    모든 데이터를 한꺼번에 전부 삭제

 

 : DROP - 데이터와 테이블 전체를 삭제

             사용하고 있던 공간도 모두 반납

             인덱스나 제약 조건 등 오브젝트도 삭제

 

 

Oracle DB 11g에서 추가된 기능

 : 읽기 전용 테이블로 변경

ALTER TABLE 테이블명 read only;

 : 읽기 전용 테이블을 다시 읽기/쓰기 모드로 변경

ALTER TABLE 테이블명 read write;

 : 가상 컬럼

   - 데이터는 입력하지 않았지만 자동으로 태이블 생성 시에 계산된 식의 값으로 입력됨

CREATE TABLE 테이블명(
	컬럼1 NUMBER,
    컬럼2 NUMBER,
    컬럼3 NUMBER GENERATED ALWAYS AS (컬럼1 + 컬럼2) ← 다른 계산식으로 넣어도 됨(case도 가능)
);

 

 

Data Dictionary (데이터 딕셔너리)

 : 오라클은 데이터베이스를 운영하기 위한 정보들을 모두 특정한 테이블에 모아두고 관리하는데,

   그 테이블을 데이터 딕셔너리라 이야기함

 : 딕셔너리에는 아래와 같은 주요 정보들이 저장됨 (아래 정보 이외에도 아주 많은 정보들이 저장됨)

- 오라클 데이터 베이스의 메모리 구조와 파일에 대한 구조 정보
- 각 오브젝트들이 사용하고 있는 공간의 정보
- 제약 조건 정보
- 사용자에 대한 정보
- 권한이나 프로파일, 롤에 대한 정보
- 감사(Audit)에 대한 정보

 

 : 장애나 잘못 관리될 경우 오라클 데이터베이스를 사용할 수 없고

   더 심한 경우 장애 시 아예 복구 조자 할 수 없게 될 수도 있음

 : 오라클은 Base Table과 Data Dictionary View로 나누어 두고

   Base Table은 DBA라 할지라도 접근을 못하게 막음

   사용자(DBA포함)들은 Data Dictionary View를 통해서만 딕셔너리를 SELECT 할 수 있게 허용함

 : 사용자가 DDL문장을 수행하는 순간 SERVER Process가 사용자 대신 해당 딕셔너리 내용을 변경해줌

 : Base Table은 Database를 생성하는 시점에 자동으로 생성됨