#6 DDL 명령과 딕셔너리 (create, 임시 테이블, alter(컬럼 추가, 이름 변경, 크기조정, 삭제), truncate, delete

DDL : CREATE, ALTER(수정), TRUNCATE(잘라내기), DROP(삭제)

 

1. CREATE

1.1 일반 테이블 생성하기

CREATE TABLE  new_table

( no NUMBER(3),

  name VARCHAR2(10),

  birth  DATE

);

 

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

no NUMBER(3) DEFAULT 0

DEFAULT 키워드 뒤에 값을 주면 된다.

 

테이블 생성 시 제한사항

- 테이블 이름은 반드시 문자로 시작

- 테이블/컬럼 이름 -> 30바이트

- 테이블 이름 -> 사용자 한명당 테이블 식별자 하나

- 테이블 이름/오브젝트 이름 -> 오라클 키워드 사용하지 않기

 

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

- 실제 데이터를 저장하지 않고 테스트 등 임시 용도로 사용

- redo 데이터를 생성하지 않아 속도가 빠르다

- 작업 마치면 데이터를 자동으로 지운다

- 세션 별로 생성 -> 다른 사용자의 데이터 볼 수 없음

- db 임시 작업, 연습용, 디버깅용

 

CREATE GLOBAL TEMPORARY TABLE table_name

( 컬럼1 데이터 타입, 

 컬럼2 데이터 타입, ............)

ON COMMIT [ delete | preserve ] ROWS;

 

- ON COMMIT DELETE ROWS : commit 하면 임시테이블에 입력된 row들을 다 지움

- ON COMMIT PRESERVE ROWS : commit 시에도 임시 테이블에 변동이 있는 row를 지우지 않겠다.

 

INSERT 할 때

- 컬럼 일부만 사용해서 insert를 할 때는 필요한 컬럼을 적어주고 values에다가 그 컬럼에 해당하는 값들을 적어줌

- 모든 컬럼의 값을 다 지정할 경우에는 컬럼명을 지정할 필요가 없음

 

생성되어 있는 임시 테이블 조회하기

SELECT temporary, duration

from user_tables

where table_name = 'TEMP01';

 

1.3 테이블 복사하기 (CTAS)

1) 모든 컬럼 복사 / 특정 컬럼 복사

CREATE TABLE dept3

AS

 SELECT * FROM dept2;

 

CREATE TABLE dept4

AS

  SELECT dcode, dname

  FROM dept2;

 

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

CREATE TABLE dept5

AS SELECT * FROM dept2

    WHERE 1 = 2;

 

- WHERE 조건절이 항상 false 이므로 테이블의 행은 가져오지 않음

(테이블 구조만 가져옴)

- 데이터 필요 없이 테이블 구조만 가져올 때 사용

 

2. ALTER 

수정하기, 테이블 내용 변경

ALTER TABLE 테이블명

- ADD( );

- MODIFY();

- RENAME COLUMN _ TO _;

- DROP COLUMN

1) 새로운 컬럼 추가하기

ALTER TABLE 테이블명

ADD ( 추가할_컬럼명 DataType );

 

- 컬럼 추가하면서 기본값 지정하기

datatype 뒤에 DEFAULT '기본값' 써주면 된다.

 

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

ALTER TABLE 테이블명

RENAME COLUMN old_컬럼명 TO 바꿀이름;

컬럼명을 지정한 이름으로 변경

 

- 테이블 이름 변경하기

RENAME old_테이블명 TO 새이름;

 

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

ALTER TABLE 테이블명

MODIFY( 대상컬럼 new_datatype );

 

4) 컬럼 삭제하기

ALTER TABLE 테이블명

DROP COLUMN 삭제할 컬럼

[ CASCADE CONSTRAINTS ];

- 다른 테이블에서 삭제하려는 컬럼을 참조하고 있는 경우 CASCADE CONSTRAINTS 옵션을 지정해줘야 에러가 안남

 

 

3. TRUNCATE  vs  DROP  vs  DELETE

DELETE (DML)	TRUNCATE	DROP
일부 행을 지움
- 테이블에 할당되어있던 원래 크기의 공간은 유지됨 * 되돌릴 수 있음
DELETE FROM 테이블명 WHERE 조건;

 

4.1 읽기 전용 테이블로 변경하기

ALTER TABLE 테이블명 read only;

- 키워드 지정

- 읽기 전용 모드는 속도가 빠름

-> 쓰기 가능한 모드에서는 rollback이 가능하도록 table 의 변경사항을 추적할 수 있도록 관리해야 한다.

    그러나 읽기 전용 table인 경우는 그 부담이 적다.

- 읽기 전용모드에서 신규컬럼 추가는 가능하다.

 

- 다시 읽기/쓰기모드 변경

alter table 테이블명 read write;

 

- 읽기 전용 상태의 테이블은 삭제할 때

DROP TABLE 테이블명

 

4.2 가상 컬럼

가상컬럼

- 사용자가 데이터를 입력할 수 없다.

- 

 

- 가상 컬럼 테이블 생성하기

CREATE TABLE vt1

( col1 NUMBER,

 col2 NUMBER,

 col3 NUMBER GENERATED ALWAYS AS (col1 + col2)

);

-> 가상컬럼은 col1과 col2가 변경이 가해지면 그 값을 계산해서 col3의 값으로 넣어줌

 

- 새로운 가상 컬럼 추가하기

ALTER TABLE vt1

ADD ( col4 GENERATED ALWAYS AS (식) );

 

5. 데이터 딕셔너리

- 오라클 데이터베이스의 메모리 구조와 파일에 대한 구조 정보들

- 각 오브젝트들이 사용하고 있는 공간들의 정보들

- 제약 조건 정보들

- 사용자에 대한 정보들

- 권한이나 프로파일, 롤에 대한 정보들

- 감사Audit 에 대한 정보들

 

1) Static Dictionary

- USER_xxx  : 해당 사용자가 생성한 오브젝트들만 조회

- ALL_xxx  : 해당 사용자가 접근 가능한 모든 정보들이 다 포함

- DBA_xxx  : DBA권한을 가진 사람만 접근할 수 있음

 

2) Dynamic Dictionary

- V$_xxx  : 오라클 DBMS가 수행되고 유지되면서 동적으로 생성되는 정보들이 담겨있다.