DDL

DDL (Data Definition Language) - CREAT (생성) , ALTER (수정) , TRUNCATE (잘라내기) , DROP (삭제)

1.CREATE

※ 테이블 생성시 제한 사항

1. 테이블 이름은 반드시 문자로 시작해야 한다. 숫자로 시작은 불가능하고 포함되는것은 가능

2. 테이블 이름이라 컬럼 이름은 최대 30 bytes 까지 가능, 한글로 테이블 이름을 생성할 경우 최대 15글자까지

3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없다.

ex) scott 사용자가 테이블명을 test로 생성한 후 인덱스 이름을 test로 동일하게 사용하는게 불가능

scott 사용자가 test 테이블을 생성하고 다른 사용자인 hr 사용자도 test 테이블을 사용가능

4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 않기를 권장

오라클 키워드라 함은 오라클에서 사용하는 미리 정해진 SELECT , FROM 들과 같은 단어를 말함

1) 일반 테이블 생성하기

SCOTT>CREATE TABLE ddl_test

2    ( no       NUMBER(3) ,

3      name   VARCHAR(10) ,

4      birth     DATE               DEFAULT SYSDATE ) ;

 

ddl_test 명의 테이블생성, no라는 컬럼은 숫자 데이터만 들어갈 수 있으며 최대길이는 3자리

name 컬럼은 가변형 문자가 들어갈 수 있으며 최대길이는 10bytes

birth 컬럼은 날짜 데이터만 들어갈 수 있으며 아무 값도 안들어올 경우 기본값 으로 현재 날짜 자동입력

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

SCOTT>CREATE TABLE ddl_test2

2    ( no           NUMBER(3,1)     DEFAULT 0 ,

3      name       VARCHAR2(10)   DEFAULT 'NO' ,

4      hiredate    DATE                DEFAULT SYSDATE ) ;  

1) 에서 생성한 테이블은 사용자가 값을 입력하지 않을 경우 기본적으로 NULL값이 자동입력된다.

그러나 DEFAULT 적고 뒤에 값을 적어놓으면 사용자가 값을 입력하지 않을 경우 DEFAULT 뒤에 적힌 값을 자동으로 넣으라는 의미

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

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

트랜잭션이 끝나거나 세션이 종료되면 테이블의 데이터는 사라짐

문법 : 

CREATE GLOBAL TEMPORARY TABLE 테이블명

( 컬럼1 데이터타입 ,

   컬럼2 데이터타입 ,

   ON COMMIT [delete|preserve]ROWS ;

위 문법에서 ON COMMIT delete ROWS 를 사용하면 COMMIT 시 데이터를 삭제

ON COMMIT preserve ROWS 를 사용하면 세션이 종료되야 데이터가 사라짐

기본값은 ON COMMIT delete ROWS

특징 :

1. Redo Log 를 생성하지 않습니다.

2. Index , View , Trigger 를 생성할 수 있으나 이 오브젝트 들의 타입도 전부 Temporary 입니다.

3. 이 테이블에 들어 있는 데이터는 이전이나 백업을 할 수 없습니다.

4) 테이블 복사하기 (CTAS)

새로운 테이블을 생성 할 때 기존에 만들어져 있던 테이블을 복사

1) 모든 컬럼 다 복사하기

SCOTT>CREATE TABLE dept3

2    AS

3    SELECT * FROM dept2 ;

-> dept2 테이블과 똑같은 컬럼과 데이터를 가진 dept3 테이블 생성

2) 특정 컬럼만 복사하기

SCOTT>CREATE TABLE dept4

2    AS

3    SELECT dcode, dname

4    FROM dept2 ;

-> dept2 테이블의 dcode, dname 컬럼만 복사해서 dept4 테이블 생성

3) 테이블의 구조(컬럼) 만 복사하고 데이터는 안 가져오기

SCOTT>CREATE TABLE dept5

2    AS

3    SELECT * FROM dept2

4    WHERE 1=2 ;       (틀린 조건만 적어주면 됨)

-> dept2 테이블의 데이터는 제외하고 컬럼만 복사해서 dept5 테이블 생성

5) 가상컬럼 테이블 생성하기 (11g 부터 추가됨)

step1. 가상 컬럼을 가지는 vt001 테이블 생성

- SCOTT>CREATE TABLE vt001

2    ( no1 number ,

3      no2 number , 

4      no3 number GENERATED ALWAYS AS (no1 + no2) VIRTUAL ) ;

-> no3 = no1 + no2 값을 가지는 가상컬럼

step2. vt001 테이블에 데이터 입력

- SCOTT>INSERT INTO vt001 VALUES (1,2,3) ;        -> no3 은 가상컬럼이기에 값을 INSERT 할수 없다.

- SCOTT>INSERT INTO vt001(no1,no2) 

2       VALUES (1,2) ;

-> no1 컬럼에 1 을 넣고 no2 컬럼에 2 를 넣으면 가상컬럼 no3 은 자동으로 no1 + no2 값이 입력된다.

2. ALTER

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

테이블 같은 경우 컬럼을 추가하거나 삭제하거나, 컬럼 이름이나 테이블 이름을 바꾸는 등의 작업

※ ALTER 명령어 사용시 사용량이 많을 때, 절대 사용하면 안됨. 디비가 죽을 수 도 있음

1) 새로운 컬럼 추가하기 

SCOTT>ALTER TABLE dept6

2    ADD (LOC VARCHAR2(10) ;

-> dept6 테이블에 LOC라는 컬럼을 추가

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

SCOTT>ALTER TABLE dept6 RENAME COLUMN LOC TO AREA ;

-> dept6 테이블의 LOC 컬럼명을 AREA 로 변경

테이블명 변경은 그냥 RENAME 명령어로 변경하면됨

SCOTT>RENAME dept6 TO dept7 ;

-> dept6 테이블명을 dept7 로 변경

3) 컬럼의 데이터 크기를 변경하기

SCOTT>ALTER TABLE dept7

2    MODIFY(dcoe, VARCHAR2(10)) ;

-> 기존에 dcode 데이터 크기 6bytes 에서 10bytes 로 변경

4) 컬럼 삭제하기

SCOTT>ALTER TABLE dept7 DROP COLUMN loc ;

-> dept7 테이블의 loc 라는 컬럼 삭제

만약 참조키로 설정되어 있는 부모 테이블의 컬럼을 삭제하려 할 경우 에러가 발생하는데 이땐 아래와 같은 방법사용

SCOTT>ALTER TABLE dept7 DROP COLUMN loc CASCADE CONSTRAINTS ;

5) 읽기 전용 테이블로 변경 -11g 에서 생김

테이블의 내용을 모두 변경은 할 수 없게 하고 조회만 가능하게 설정

SCOTT>ALTER TABLE t_read read only ;

-> t_read 테이블을 읽기 전용으로 변경  (read only 부분을 read write 로 바꾸면 읽고 쓰기 가능)

3. TRUNCATE 명령

테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납하는 명령어, 테이블 자체가 삭제되지는 않음

또한 테이블에 생성되어 있던 인덱스의 내용도 함께 Truncate 됨

SCOTT>TRUNCATE TABLE dept7 ;

4. DROP 명령

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

SCOTT>DROP TABLE dept7 ;

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

DELETE - 데이터는만 지워지고 디스크 상의 공간은 그대로 남아있음

TRUNCATE - 최초에 테이블이 만들어졌던 상태, 데이터가 하나도 없는 상태로 모든 데이터를 삭제하고 컬럼값만 남김

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

6. 데이터 딕셔너리 (Dictionary)

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

딕셔너리에 저장되는 정보

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

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

- 제약 조건 정보들

- 사용자에 대한 정보들

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

- 감사(Audit) 에 대한 정보들

- 이러한 정보들은 중요한 정보이기에 오라클은 이 딕셔너리를  Base table 과 Data Dictionary View 로 나누어 두고, 

base table 은 DBA라 할 지 라도 접근을 못하게 막아놓음.

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

- base table 은 database를 생성하는 시점에 자동으로 만들어짐

- data dictionary view 는 catalog.sql 이란 파일이 수행되어야 만들어짐

- catalog.sql 이란 파일은 DBCA로 database를 생성 할 때는 자동으로 수행되지만 create database 라는 명령어로 수동으로 database를 생성할 때는 수행되지 않으므로 DBA가 수동으로 생성해야함

Data Dictionary View 의 종류

1. Static Data Dictionary View

- 접두어가 DBA_ , ALL_ ,USER_ 로 시작되는 3가지

USER_ = 해당 사용자가 생성한 오브젝트들만 조회 할 수 있다

ALL_ = 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트를 조회 할 수 있다

DBA_ = 데이터베이스 내의 거의 모든 오브젝트들을 다 볼수 있지만 DBA권한을 가진 사람만이 이 딕셔너리를 조회             할 수 있다

2. Dynamic Performance View

- 접두어가 V$ 로 시작하는 것