[SQL] 5. DDL과 Data Dictionary

명령어
DDL (Data Definition Language)	CREATE ALTER TRUNCATE DROP	생성 수정 잘라내기 삭제
DML (Data Manipulation Language)	INSERT UPDATE DELETE MERGE	입력 변경 삭제 병합
DCL (Data Control Language)	GRNAT REVOKE	권한주기 권한뺏기
TCL (Transaction Control Language)	COMMIT ROLLBACK	확정 취소

DDL (Data Definition Language)

CREATE

새로운 오브젝트나 스키마를 생성. (이번장에서는 table만)

테이블 생성시 제한사항

1. 테이블 이름은 반드시 문자로 시작. 특수문자도 가능하지만 테이블 생성시 “ (겹따옴표) 로 감싸야 하며 권장하지 않음.
2. 테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능. 즉 한글로 테이블 이름을 생성할 경우 최대 15글자 까지만 가능.
3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없음. ( 예를 들어 scott 사용자가 테이블명을 test 로 생성한 후 다른 테이블 이름을 test 로 동일하게 사용할 수 없다는 것. 그러나 다른 사용자인 hr 사용자는 test 테이블 생성할 수 있음)
4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 말것. (미리 정해진 SELECT , FROM 등과 같은 키워드로 생성이 안되는 것은 아니지만 사용하지 않는것을 권장.)

(1) 일반 테이블 생성하기

SCOTT>CREATE TABLE new_table
  2  ( no    NUMBER(3),
  3    name  VARCHAR2(10),
  4    birth DATE ) ;
Table created.

No 컬럼은 숫자(NUMBER) 데이터만 들어갈 수 있고 최대 길이는 3자리. Name 컬럼은 가변형 문자(VARCHAR2), 최대 길이는 10 bytes 까지. Birth 컬럼은 날짜(DATE) 데이터.

값을 입력하지 않을경우 NULL값이 자동으로 입력됨. 

(2) 기본 입력 값 설정과 함께 테이블 생성하기

 SCOTT>CREATE TABLE tt02
  2  ( no        NUMBER(3,1)    DEFAULT 0,
  3    name      VARCHAR2(10)   DEFAULT 'NO NAME',
  4    hiredate  DATE           DEFAULT SYSDATE ) ;

  Table created.

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

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

CREATE GLOBAL TEMPORARY TABLE 테이블명
( COL1  데이터타입,
  COL2  테이터타입,  ...., )
ON COMMIT [delete | preserve] ROWS ;

delete : COMMIT시 데이터삭제(default)

preserve : 세션종료시 데이터삭제

1. 저장 목적이 아닌 임시 작업용(테스트나 조회용)으로 이 테이블은 마치 뷰 처럼 테이블을 생성하면 그 정의만 딕셔너리에 저장돼 있다가 사용자가 해당 테이블에 엑세스하면 메모미 상에 해당 테이블을 만들고 데이터를 가져옴.
2. 예를 들어 어떤 기능을 테스트를 하기 위해 데이터가 잠시 필요할 경우 일반 테이블을 만들고 데이터를 입력하면 리두 로그가 생성돼 시간도 오래 걸리는데 Temporary Table 을 사용할 경우 아주 빠르게 생성됨. 그리고 작업이 끝나면 자동으로 내용을 삭제까지 해주므로 편함.
3.그리고 세션별로 만들어지기 때문에 다른 세션에서 테이블을 공유할 수 없다.

다른 특징들

1. Redo Log 를 생성하지 않음.
2. Index , View , Trigger 를 생성할 수 있으나 이 오브젝트들의 타입도 전부 Temporary 임.
3. 이 테이블에 들어 있는 데이터는 이전이나 백업을 할 수 없음.

실습1. 터미널 2개 열어서 한쪽에서 생성후 다른쪽에서 조회하기

세션1

SCOTT>CREATE GLOBAL TEMPORARY TABLE temp01
  2  (  no    NUMBER ,
  3     name  VARCHAR2(10) )
  4  ON COMMIT delete ROWS;

Table created.

SCOTT>INSERT INTO temp01 VALUES(1, 'AAAAA');

1 row created.

SCOTT>SELECT * FROM temp01;

        NO NAME
---------- ----------
         1 AAAAA

세션2

SQL> SELECT * FROM temp01;

no rows selected

이와같이 다른 터미널에서는 조회 안됨. 같은 계정이라도 다른 창에서는 다른 사람이 어떤 작업을 하는지 알수 없다.
세션 1에서 아직 COMMIT 을 수행하지 않았기 때문에 temp01 테이블에 데이터가 있지만 TEMPORARY TABLE 생성 옵션이 ON COMMIT delete ROWS 이므로 커밋을 하면 모두 삭제됨

실습2. 생성되어 있는 temporary table 조회하기

SCOTT>SELECT temporary, duration
  2  FROM user_tables
  3  WHERE table_name = 'TEMP01';

T DURATION
- ------------------------------
Y SYS$TRANSACTION

Temporary 여부 -> Y(YES)

Duration -> transaction 이므로 commit 이나 rollback을 수행하는 동안 유지된다는 뜻.

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

새로운 테이블을 생성 할 때 기존에 만들어져 있는 테이블을 참조하여 생성하는 방법.

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

SCOTT>CREATE TABLE dept3
  2  AS
  3    SELECT * FROM dept2;

Table created.

SCOTT>CREATE TABLE dept4
  2  AS
  3    SELECT dcode, dname
  4    FROM dept2;

Table created.

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

SCOTT>CREATE TABLE dept5
  2  AS SELECT * FROM dept2
  3     WHERE 1 = 2 ;
Table created.

SCOTT>SELECT * FROM dept5;
no rows selected

이 방법은 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식. 3번 WHERE 줄에 틀린 조건을 줄 경우 그 조건에 해당되는 데이터가 없기 때문에 데이터는 못 가져오고 테이블 구조만 생성하는 것

(5) 가상 컬럼 테이블 생성하기

step1 : 가상 컬럼을 가지는 vt1 테이블 생성

SCOTT>CREATE TABLE vt1
  2  ( col1 NUMBER,
  3    col2 NUMBER,
  4    col3 NUMBER GENERATED ALWAYS AS (col1 + col2) );

Table created.

col3은 col1+col2의 값을 가지는 가상컬럼.

step2 : vt1 테이블에 데이터 입력

SCOTT>INSERT INTO vt1 VALUES (1,2,3);
INSERT INTO vt1 VALUES (1,2,3)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SCOTT>INSERT INTO vt1 (col1, col2) VALUES (1,2);

1 row created.

위와 같이 가상 컬럼에는 사용자가 데이터를 입력못함

step3 : 입력된 데이터 조회

SCOTT>SELECT * FROM vt1 ;

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3

col3에는 데이터를 입력하지 않았지만 자동으로 테이블생성시 계산된 식의값으로 입력됨을 알수있음

step4 : 기존 값 변경후 가상 컬럼에 반영되는지 확인

SCOTT>UPDATE vt1
  2  SET col1 = 5;

1 row updated.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3
---------- ---------- ----------
         5          2          7

기존컬럼 값이 변경될 경우 즉시 가상 컬럼에도 반영됨

step5 : 새로운 가상 컬럼 추가

SCOTT>ALTER TABLE vt1
  2  ADD (col4 GENERATED ALWAYS AS ((col1+col2)+col2));

Table altered.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         5          2          7          9

step6 : 테이블에서 가상컬럼 내역조회

SCOTT>SELECT column_name,
  2          data_type,
  3          data_default
  4  FROM  user_tab_columns
  5  WHERE table_name = 'VT1'
  6  ORDER BY column_id;

COLUMN_NAM DATA_TYPE  DATA_DEFAULT
---------- ---------- -------------------------
COL1       NUMBER
COL2       NUMBER
COL3       NUMBER     "COL1"+"COL2"
COL4       NUMBER     "COL1"+"COL2"+"COL2"

step7 : 조건절을 활용한 가상 컬럼 생성

SCOTT>CREATE TABLE sales10
  2  ( no     NUMBER,
  3    pcode  CHAR(4),
  4    pdate  CHAR(8),
  5    pqty   NUMBER,
  6    pbungi NUMBER(1)
  7     GENERATED ALWAYS AS
  8      (
  9        CASE
 10            WHEN SUBSTR(pdate,5,2) IN ('01','02','03') THEN 1
 11            WHEN SUBSTR(pdate,5,2) IN ('04','05','06') THEN 2
 12            WHEN SUBSTR(pdate,5,2) IN ('07','08','09') THEN 3
 13            ELSE 4
 14        END  ) virtual );

Table created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(1,'100','20110112',10);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(2,'200','20110505',20);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(3,'300','20110812',30);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(4,'400','20111024',40);

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM sales10;

        NO PCOD PDATE          PQTY     PBUNGI
---------- ---- -------- ---------- ----------
         1 100  20110112         10          1
         2 200  20110505         20          2
         3 300  20110812         30          3
         4 400  20111024         40          4

ALTER

만들어져 있는 오브젝트를 변경하는 명령어.
테이블의 경우, 컬럼을 추가하거나 삭제하고 컬럼이름이나 테이블 이름을 바꾸는 등의 작업을 할때 사용함.(부하가 많이 걸리는 명령어니 사용량이 많은 시간에 수행하는 것은 위험)

(1) 새로운 컬럼 추가하기

SCOTT>CREATE TABLE dept6
  2  AS
  3    SELECT dcode, dname
  4    FROM   dept2
  5    WHERE  dcode IN(1000,1001,1002);

Table created.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME
------ ------------------------------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

위의 코드에서 LOCATION 컬럼 추가

SCOTT>ALTER TABLE dept6
  2  ADD (location VARCHAR2(10));

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION
------ ------------------------------ ----------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

기본값으로 NULL값으로 입력됨. NULL 이외의 다른 값을 기본값으로 입력하고 싶다면 DEFAULT 값 지정하면 됨.

SCOTT>ALTER TABLE dept6
  2  ADD (location2 VARCHAR2(10) DEFAULT 'SEOUL');

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION   LOCATION2
------ ------------------------------ ---------- ----------
1000   Management Support Team                   SEOUL
1001   Financial Management Team                 SEOUL
1002   General affairs                           SEOUL

(2) 컬럼이름 변경하기

SCOTT>ALTER TABLE dept6
  2  RENAME COLUMN location2
  3  TO loc;

SCOTT>RENAME dept6 TO dept7; // 테이블 이름변경

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

SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(10)

SCOTT>ALTER TABLE dept7
  2  MODIFY (loc  VARCHAR2(20));

Table altered.

SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(20)

(4) 컬럼 삭제하기

SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc;

SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc CASCADE CONSTRAINTS;

참조키로 설정돼있는 부모테이블의 컬럼을 삭제 할 경우에는 두번째처럼 제약조건을 설정하면됨.

(5) 읽기 전용 테이블로 변경하기

SCOTT>CREATE TABLE t_readyonly
  2  ( no NUMBER,
  3    name VARCHAR2(10) ) ;

Table created.

SCOTT>INSERT INTO t_readyonly
  2  VALUES (1, 'AAA');

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM t_readyonly;

        NO NAME
---------- ----------
         1 AAA

SCOTT>ALTER TABLE t_readyonly read only;

Table altered.

읽기전용으로 변경후 테이블에 데이터 INSERT하거나 컬럼추가 하려고 하면 에러남. DROP TABLE로 테이블은 삭제가능.

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

SCOTT>ALTER TABLE t_readonly read write;

현재 테이블이 읽기 전용인지를 조회하려면 user_tables 의 read_only 컬럼의 값이 YES 이면 읽기전용이고 NO 이면 읽기/쓰기 모드.

TRUNCATE

테이블의 데이터를 전부삭제. 테이블은 유지.
해당 테이블의 데이터가 모두 삭제되지만 테이블 자체는 지워지는 것이 아니며 생성되어 있던 인덱스의 내용도 함께 TRUNCATE됨.

SCOTT>TRUNCATE TABLE dept7;

DROP

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

SCOTT>DROP TABLE dept7;

DELETE, TRUNCATE, DROP 비교

DELETE : 데이터만 지워짐. 디스크상의 공간은 그대로가짐 원하는 데이터만 삭제가능.(테이블 용량 줄어들지 않음)

TRUNCATE : 최초에 테이블이 만들어졌던 상태로. 모든 데이터를 삭제하고 컬럼값만 남겨놓음. 전부삭제(용량도 줄어들고 인덱스도 truncate)

DROP : 데이터와 테이블을 전부 삭제. 사용하고 있던 공간도 모두 반납. 인덱스, 제약조건, 오브젝트 모두삭제.

Data Dictionary

오라클은 데이터베이스를 운영하기 위한 정보들을 모두 특정테이블들에 모아두고 관리하는데 그 테이블들을 데이터 딕셔너리 라고 한다. 이 딕셔너리에는 아래와 같은 주요 정보들이 저장됨.

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

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

제약 조건 정보들

사용자에 대한 정보들

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

감사(Audit) 에 대한 정보들

이외에도 많은 정보들이 저정되어 있고, 이 정보들은 사람으로 비유하면 두뇌에 해당 되는 정보들이기 때문에 만약 장애나 잘못 관리 될 경우 오라클 데이터베이스를 사용할 수 없고 더 심할 경우 장애 시 아예 복구조차 할 수 없게 될 수도 있다. 그래서 이 딕셔너리를 Base Table 과 Data Dictionary View 로 나누어 두고 Base Table 은 DBA 라 할 지라도 접근을 못하게 막아놨다. 그리고 사용자(DBA 포함)들은 Data Dictionary View 를 통해서만 딕셔너리를 SELECT 할 수 있게 허용함.

만약 데이터베이스에 변경 사항이 생겨 (예를 들어 새로운 테이블이 생성된다든지 하는 상황) 딕셔너리 내용을 변경해야 할 경우 사용자가 직접 수동으로 딕셔너리를 변경하지 못하고 해당 DDL 문장을 수행하는 순간 SERVER Process 가 사용자를 대신해서 해당 딕셔너리 내용을 변경해 줌.

Base Table 은 Database 를 생성하는 시점에 자동으로 만들어 짐 (Create Database 나 DBCA를 이용하거나 마찬가지)

반면 Data Dictionary View 는 Catalog.sql 이란 파일이 수행되어야만 만들어 짐. 이 파일은 DBCA 로 Database 를 생성 할 때는 자동으로 수행되지만 CREATE DATABASE 라는 명령어로 수동으로 Database 를 생성할 때는 수행되지 않으므로 DBA 가 수동으로 생성해야만 함.

Data Dictionary View 는 크게 2가지 종류가 있다.

• Static Data Dictionary View
  • USER_XXX : 해당 사용자가 생성한 오브젝트만 조회 할수 있음.
  • ALL_XXX : 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트를 조회 할 수 있음.
  • DBA_XXX : 데이터베이스 내의 거의 모든 오브젝트들을 볼 수 있지만 DBA권한을 가진 사람만 이 딕셔너리를 조회 할 수 있음.
• Dynamic Performance View
  • v$XXXX

STATIC DATA DICTIONARY VIEW

연습용 테이블 static_table 생성하고 데이터입력

SCOTT>CREATE TABLE st_table
  2  (no number);

Table created.

SCOTT>BEGIN
  2     FOR i IN 1..1000 LOOP
  3        INSERT INTO st_table VALUES (i);
  4     END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

데이터 딕셔너리를 조회해 해당 테이블에 데이터가 몇 건 있는지 확인

SCOTT>SELECT COUNT(*) FROM st_table;

  COUNT(*)
----------
      1000

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------

실제 데이터는 1000건이 있지만 딕셔너리 내용이 변경이 안되서 값이 조회 되지 않음.

딕셔너리 수동으로 업데이트 한 후 다시 조회

SCOTT>ANALYZE TABLE st_table COMPUTE STATISTICS;

Table analyzed.

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------
      1000          5

ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어

Dynamic Performance View

Static Data Dictionary 와 반대로 Dynamic Performance View 는 정보를 실시간으로 조회하게 되며 사용자가 Dynamic Performance View 에 조회를 하게 되면 오라클은 해당 정보를 Control file 이나 현재 메모리에서 조회하여 보여준다.

연습문제

1) 아래와 같은 구조의 일반 테이블을 생성하시오.

SCOTT>CREATE TABLE new_emp
  2  ( no   NUMBER(5),
  3    name VARCHAR2(20),
  4    hiredate DATE,
  5    bonus NUMBER(6,2) );

Table created.

2) 위 1번 문제에서 생성한 new_emp 테이블에서 NO , NAME , HIREDATE 컬럼만 가져와서 아래 그림과 같이 new_emp2 테이블을 생성하는 쿼리를 쓰세요.

SCOTT>CREATE TABLE new_emp2
  2  AS
  3    SELECT no, name, hiredate
  4    FROM new_emp;

3) 위 2번 문제에서 생성한 new_emp2 테이블과 동일한 구조의 테이블을 new_emp3 이름으로 생성하되 테이블 구조만 가져오고 데이터는 가져오지 않도록 하는 쿼리를 쓰세요.

SCOTT>CREATE TABLE new_emp3
  2  AS
  3    SELECT no, name, hiredate
  4    FROM new_emp2
  5    WHERE 1 = 2;

4) 위 2번 문제에서 생성한 new_emp2 테이블에 DATE 타입을 가진 BIRTHDAY 컬럼을 추가하는 쿼리를 쓰세요. 단 해당 컬럼이 추가될 때 기본값으로 현재날짜 ( SYSDATE ) 가 자동으로 입력되도록 하세요.

SCOTT>ALTER TABLE new_emp2
  2  ADD (birthday DATE DEFAULT SYSDATE);

5) 위 4번 문제에서 생성한 new_emp2 테이블의 BIRTHDAY 컬럼 이름을 BIRTH 로 변경하는 쿼리를 쓰세요.

SCOTT>ALTER TABLE new_emp2
  2  RENAME COLUMN birthday TO birth;

6) 위 4번 문제에서 생성한 new_emp2 테이블의 NO 컬럼의 길이를 NUMBER(7) 로 변경하는 쿼리를 쓰세요

SCOTT>ALTER TABLE new_emp2
  2  MODIFY (no NUMBER(7));

7) new_emp2 테이블의 컬럼 중에서 BIRTH 컬럼을 삭제하는 쿼리를 쓰세요.

SCOTT>ALTER TABLE new_emp2
  2  DROP COLUMN birth;

8) new_emp2 테이블의 컬럼은 남겨 놓고 데이터만 지우는 쿼리를 쓰세요.

SCOTT>TRUNCATE TABLE new_emp2;

9) new_emp2 테이블을 완전히 삭제하는 쿼리를 쓰세요

SCOTT>DROP TABLE new_emp2;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저