5장. DDL 명령과 딕셔너리

DDL : 'Data Definition Language'의 약자. 데이터를 저장하는 언어인 SQL.

 

[ 다양한 오라클 명령어 종류들 ]

구분	의미	종류
DML (Data Manipulation Language)	데이터를 저장하고 사용하는 명령어	INSERT (입력) UPDATE (변경) DELETE (삭제) MERGE (병합)
DDL (Data Definition Launguage)	정의한 데이터를 관리하는 명령어	CREATE (생성) ALTER (수정) TRUNCATE (잘라내기) DROP (삭제)
DCL (Data Control Language)	권한을 주거나 뺏는 명령어	GRANT (권한 주기) REVOKE (권한 뺏기)
TCL (Transaction Control Language)	작업 내역을 확정 또는 취소하는 명령어	COMMIT (확정) ROLLBACK (취소)
SELECT	DML에 넣기도 한다. 별도로 실제 SQL에서는 SELECT로, 상당한 분량을 가지고 있다.	어떤 분류에서는 DQL(Data Query Language)라고 하기도 한다.

 

 

 

 

---

 

 

 

 

1. CREATE - 새로 생성하라

 

1) 일반 테이블 생성하기

테이블 이름은 소문자로 작성할 수 있지만, 대문자로 작성하는 것이 좋다.

CREATE TABLE new_table
(
    col1 NUMBER(3),
    col2 VARCHAR2(10), # varchar: variable charactor의 약자
    col3 DATE
);

 

 

 

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

오라클도 마찬가지로 기본값을 설정할 수 잇다. 값을 입력하지 않은 상태는 default 상태이다.

CREATE TABLE new_table
(
    col1 NUMBER(3, 1) DEFAULT 0,
    col2 VARCHAR2(10) DEFAULT 'NO Name',
    col3 DATE DEFAULT SYSDATE
);

# Table created.


INSERT INTO new_table (col1) VALUES(1);

# 1 row created

 

 

 

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

영어로 테이블 이름을 작성할 때도 있지만, 한글로도 많이 작성한다. 보통 만들 때는 영문으로 작성한다.

Localication, 지역 맞춤화. 오라클은 해당 나라에 맞도록 언어, 단위 등을 맞춰준다.

CREATE TABLE 한글테이블
(
    컬럼1 데이터-타입,
    컬럼2 데이터-타입,
    컬럼3 데이터-타입
);

 

 

 

---

 

※ 참고

 

테이블을 생성할 때 아래와 같은 제한사항이 있다.

 

1. 테이블은 반드시 문자로 시작해야 한다. 즉, 숫자로 시작할 수 없고 숫자가 포함되는 것은 가능하다. 특수문자도 가능하지만 테이블 생성 시 큰따옴표로 감싸야 하며 권장하지 않는다.
2. 테이블 이름이나 컬럼 이름은 최대 30바이트까지 가능하다. 한글로 테이블 이름을 생성할 경우 최대 15글자까지만 가능하다.
3. 테이블 이름은 한 명의 사용자가 다른 오브젝트의 이름과 중복으로 사용할 수 없다. 그러나 그 이외의 다른 사용자는 해당 테이블 이름을 사용할 수 있다.
4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 않기를 권장한다. 생성이 안 되는 것은 아니지만 사용 시에 아주 불편하고 위험할 수도 있기 때문에 절대로 사용하지 않기를 권장한다.

 

---

 

 

 

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

 Oracle 8 버전부터 등장했다. 실제 데이터를 저장하지 않고 테스트 등 임시 용도로 사용한다. 리두 데이터 생성하지 않아서 속도가 빠르다. 작업을 마치면 데이터를 자동으로 지운다. 세션 별로 만들어져서 다른 사용자의 데이터를 볼 수 없다. 일반적인 Table, Reboot는 데이터가 남아 있다. 복구를 위한 리두 데이터를 생성하지 않아 속도가 빠르다.

 

CREATE GLOBAL TEMPORARY TABLE 테이블명
(
컬럼1 데이터-타입,
컬럼2 데이터-타입,
···,
ON COMMIT [ delete | preserve ] ROWS;

 

commit 수행 후 삭제된다.

 

 

(2) 생성되어 있는 Table 조회하기

SELECT 원하는-컬럼
FROM user_tables
WHERE 조건식;

 

 

 

5) 테이블 복사하기 (CTAS라고도 한다)

 

(1) 모든 컬럼 다 복사하기 / 특정 컬럼만 복사하기

# 모든 컬럼 복사
CREATE TABLE table3
AS
    SELECT * FROM table2;


# 특정 컬럼만 복사
CREATE TABLE table3
AS
    SELECT col1, col2
    FROM table2;

 

 

 

(2) 테이블의 구조(컬럼)만 가져오고 데이터 안 가져오기

 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식이며 문법은 거의 동일하지만 WHERE절에 다른 조건을 적는다.

 

테이블의 구조만 복사(1단계)

CREATE TABLE table3
AS
    SELECT * FROM table2
    WHERE 조건식;

• 실데이터는 복사되지 않는다.
• no rows selected (건이 없다.)

 

 

 

 

---

 

 

 

 

2. ALTER 명령

테스트용 테이블 생성 → 새로운 컬럼 추가 → 컬럼 추가하면서 기본값 지정

이때 default 값을 추가하면 편해진다.

 

 

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

ALTER TABLE new_table RENAME COLUMN col1 To col2
RENAME new_table TO table1

 

 

3) 컬럼의 데이터 크기 조정하기

ALTER TABLE new_table
MODIFY(col1 데이터-타입); # 데이터 손실이 일어나지 않도록 주의하기

 

 

4) 컬럼 삭제하기

ALTER TABLE new_table DROP COLUMN col1;
ALTER TABLE new_table DROP COLUMN col1 CASCADE CONSTRAINTS;

* CASCADE CONSTRAINTS

관리하고 있던 데이터를 고려해서 삭제한다. 함부로 삭제하면 큰 손실이 따른다.

무조건 삭제 조건이 만족되었을 때 삭제하도록 한다. 다른 데이터들이 있을 때 삭제되면 안되기 때문.

 

 

 

 

---

 

 

 

 

3. TRUNCATE 명령

테이블 삭제 명령. 테이블의 구조는 살리고 내용이 전유한 저장 영역이 삭제된다. 내용은 완전히 날라간다.

TRUNCATE TABLE new_table;

 

 

 

 

---

 

 

 

 

4. DROP 명령

완전히 다 삭제한다. 아무 흔적 없이 지운다.

DROP TABLE new_table;

 

 

 

 

---

 

 

 

 

5. DELETE, TRUNCATE, DROP 명령어의 차이점 비교

DELETE 명령어

원하는 곳만 삭제, 'DELETE ALL'하면 모두 삭제. 저장 공간은 남는다.

 

TRUNCATE 명령어

공간을 날려버린다. 점유하고 있던 저장 능력이 날라간다.

 

DROP 명령어

테이블 구조가 완전히 다 날라간다. 아예 생성되지 않은 상태로 만든다.

 

 

 

 

 

---

 

 

 

 

6

 

1. 읽기 전용 테이블로 변경하기 - 11g New feature

테이블은 보통 읽기, 쓰기가 가능한데, 읽기만 가능한 테이블이 가능하다.

 

ALTER TABLE read_table read only;

 

 읽기 전용으로 변경된 테이블에 데이터 입력 혹은 컬럼 추가를 시도했을 경우 ' ORA-12081: update operation not allowed on table "SCOTT"."T_READONLY" ' 오류가 나타난다.

 

 읽기 전용인 테이블은 읽기 전용 상태일 때 아래의 명령으로 삭제 가능함

DROP TABLE read_table;

 

 

 

2. 가상 컬럼 테이블 생성하기 (11g부터 추가된 기능)

가상 컬럼이란 있는 것처럼 보이는 컬럼이다. 가상으로 읽는다. 가상 컬럼에는 데이터 입력이 불가하다. 위의 테이블같은 경우 col1, col2에 데이터를 입력할 수 있지만, col3에 데이터를 입력할 수 없다. 입력 코드를 작성하면 'ORA-54013: INSERT operation disallowed on virtual colums' 오류가 나타난다.

 

col4 GENERATED ALWAYS AS (연산식)
# 연산식은 col4가 포함되어 있는 테이블의 컬럼 중 사용자가 지정한 연산식을 계산한다.

 

 

 

 

 

---

 

 

 

 

7. DATA Dictionary (데이터 딕셔너리)

데이터 살연의 연속. 보통 속성적으로 일상적인 영역이 원활하게 움직일 수 있도록 도와준다.

 

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

 

 

[ Data Dictionary의 종류 ]

종류	세부 종류	의미
Static Dictionary	고정되어 있다. 사용자에 따라 달라질 수 있다.
	USER_XXX	유저가 관리하고 유지하는 데이터
	ALL_XXX	접근할 수 있는 권한의 정보를 확인한다. 일반 사용자가 등급의 허가된 데이터를 보여주는 것.
	DBA_XXX	데이터베이스 관리자가 볼 수 있는 등급의 허가된 데이터를 보여주는 것.
Dynamic Dictionary	변한다.
	V$_XXX	동적 성능 정보를 제공