[7장] DDL 문장과 딕셔너리

 

 

 

- 오브젝트(Object) : 오라클 데이터베이스 내부에 데이터를 관리하기 위한 다양한 저장 객체

- 세그먼트(Segment) : 오브젝트 중에 특별히 데이터를 저장하기 위한 별도의 저장공간을 가지는 것

 

 

1. CREATE

 

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

 

 

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

 

예제 1)  일반 테이블 생성하기

 

SCOTT> CREATE TABLE ddl_test      2      ( no NUMBER(3) ,      3           name VARCHAR2(10),      4           birth DATE DEFAULT SYSDATE      5      TABLESPACE users ;

 

[설명]

 - ddl_test라는 이름의 테이블 생성

 - No 컬럼은 숫자(NUMBER) 데이터만 들어가 수 있음 최대 길이는 3자리

 - NAME 컬럼은 가변형 문자(VARCHAR2)가 들어갈 수 있으며 최대 길이는 10 bytes

 - Brith 컬럼은 날짜(DATE)만 들어갈 수 있으며 값이 없을 경우에는 기본값(DEFAULT)로

   현재 날짜(SYSDATE)가 자동 입력

 

 

 

 (2) 한글 테이블 생성하기

 

예제 2)  한글로 테이블 생성하기

 

SCOTT> CREATE TABLE 한글테이블      2      ( 컬럼1 number ,      3        컬럼2 varchar2(10) ,      4        컬럼3 date ) ;

 

 

  - 테이블 생성 시 제한 사항

1. 테이블 이름은 반드시 문자로 시작. 즉 숫자로 시작할 수는 없고 숫자가 포함되는 것은 가능.

   특수문자도 가능하지만 테이블 생성시 "(겹따옴표) 로 감싸야 하며 권장하지 않음
2. 테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능. 즉, 한글로 테이블 이름을 생성 하실 경우

    최대 15글자 까지만 가능
3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없음

   예를 들어 scott 사용자가 테이블명을 test 로 생성한 후 인덱스 이름을 test 로 동일하게 사용할수 없다.

   그러나 scott 사용자가 test 테이블 만들어도 hr사용자는 test라는 테이블 이름을 사용할 수 있음

4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 않기를 권장합니다.
   오라클 키워드라 함은 오라클에서 사용하는 미리 정해진 SELECT , FROM 등과 같은 단어들을 말합니다.

   생성이 안되는 것은 아니지만 사용시에 아주 불편하고 위험 할 수도 있기에 절대로 사용하지 말기를 권장

 

 

 

 (3) 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) 터미널을 2 개 열어서 한쪽에서 생성 후 다른쪽에서 조회 확인하기

 

- 세션 1

 

 

 

-    세션 2

 

 

[설명] 세션1에서 COMMIT을 수행하지 않았기 때문에 조회 x

 

 

- 세션 1

 

[설명] temporary table 생성 옵션이 ON COMMIT DELETE ROWS 이므로 커밋을 하면 모두 삭제 됨 

※ 옵션을 ON COMMIT PRESERVE ROWS 로 주면 COMMIT 후에도 데이터는 남아 있으며

    해당 세션이 종료되어야 테이블의 데이터가 삭제

 

 

 

- 특징

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

 

 

 

실습 2) 생성되어 있는 Temporary Table 조회하기

 

SCOTT> SELECT temporary, duration      2       FROM user_tables      3       WHERE table_name='TEMP01' ;  T    DURATION -- ----------------  Y   SYS$TRANSACTION

 

 

 

 

 

 

 (4) 테이블 복사하기 (CTAS 라고도 합니다.)

 

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

 

1) 모든 컬럼 다 복사하기

 

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

 

 

2) 특정 컬럼만 복사하기

 

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

 

 

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

 

SCOTT> CREATE TABLE dept5      2      AS      3           SELECT *      4           FROM dept2      5           WHERE 1=2 ;

 

[설명] 이 방법은 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식

 

 

 

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

 

  : 11g 부터는 테이블을 생성 할 때 가상 컬럼이라는 것을 설정 할 수 있음

 

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

 

SCOTT> CREATE TABLE vt001      2      ( no1 number ,      3        no2 number ,      4        no3 number GENERATED ALWAYS AS (no1 + no2 ) VIRTUAL ) ;

 

 

Step 2. vt001 테이블에 데이터를 입력 

 

SCOTT> INSERT INTO vt001 VALUES (1,2,3); INSERT INTO vt001 VALUES (1,2,3)                      * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns SCOTT> INSERT INTO vt001(no1,no2)      2      VALUES(1,2); 1 row created.

 

 

 Step 3. 입력된 데이터를 조회

 

SCOTT> SELECT * FROM vt001 ;    NO1      NO2      NO3 ------ ----- -----        1         2         3

 

 

Step 4. 기존 값을 변경 한 후 가상 컬럼에 반영되는 지 확인

 

SCOTT> UPDATE vt001      2      SET no1=10 ; ​1 row updated. SCOTT> SELECT * FROM vt001 ;   NO1      NO2      NO3 ------ ----- -----       10         2         12

 

 

 

 Step5. 인덱스와 제약조건이 생성 가능한 지 테스트

 

SCOTT> INSERT INTO vt001 (no1, no2)      2      VALUES (3,4); 1 row created. SCOTT> INSERT INTO vt001 (no1,no2)      2      VALUES(6,6) ; INSERT INTO vt001 (no1,no2) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.IDX_VT001_NO3) violated

 

 

 Step 6. 새로운 가상 컬럼을 추가

 

SCOTT> ALTER TABLE vt001      2      ADD (no4 GENERATED ALWAYS AS ((no1*12)+no2)) ; Table altered. SCOTT> SELECT * FROM vt001 ;   NO1      NO2      NO3      NO3 ------ ----- ----- -----       10         2         12      122        3          4          7         40

 

 

 

 Step 7. 테이블에서 가상 컬럼 내역을 조회

 

SCOTT> set line 200 SCOTT> col column_name for a10 SCOTT> col data_type for a10 SCOTT> col data_default for a25 SCOTT> SCOTT> SELECT column_name      2                 , data_type      3                 , data_default      4      FROM user_tab_columns      5      WHERE table_name = 'VT001'      6      ORDER BY column_id ;   COLUMN_NAME     DATA_TYPE     DATA_DEFAULT -------------  -----------  --------------   NO1                      NUMBER   NO2                      NUMBER   NO3                      NUMBER            "NO1"+"NO2"   NO4                      NUMBER            "NO1"*12+"NO2"

 

 

 

 Step 8. 조건절을 활용한 가상컬럼 생성

 

​ SCOTT> CREATE TABLE panmae10      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 panmae10 (no,pcode,pdate,pqty)      2      VALUES(1,'100','20110112',10) ; 1 row created.   SCOTT> INSERT INTO panmae10 (no,pcode,pdate,pqty)      2      VALUES(2,'200','20110505',20); 1 row created.   SCOTT> INSERT INTO panmae10 (no,pcode,pdate,pqty)      2      VALUES(3,'300','20110812',30); 1 row created.   SCOTT> INSERT INTO panmae10 (no,pcode,pdate,pqty)      2      VALUES(4,'400','20111024',40); 1 row created. SCOTT> COMMIT ; Commit complete. SCOTT> SELECT * FROM panmae10 ;  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

 

 

 

 

 (6) 파티션 테이블 생성하기

 

 : 평소에는 데이터를 디스크의 파일에 안전하게 저장 해 두었다가 필요할 때 메모리(Database Buffer Cache)로

   복사를 해와서 작업

   데이터 파일에 데이터를 저장해 두고 필요할 때 마다 Database Buffer Cache 라는 곳으로 복사해 놓고 작업

 

  이때 오라클은 Database Buffer Cache 라는 메모리 공간을 Tablespace라는 논리적인 공간으로 나누어서 사용

 

  정리를 하면 오라클에서의 작업은 평소에는 디스크의 데이터파일에 내용을 안전하게 저장하고 있다가 필요할 경우

  메모리의 Tablespace 라는 공간으로 해당 테이블을 복사해 놓고 작업을 하게 된다는 뜻

 

 - 창고처럼 작은 공간을 여러개 만들어서 데이터를 분산 시키는 방법을 테이블 파티셔닝이라고 함

 

 

 

 

 

 

- 하나의 판매 테이블의 데이터를 여러 테이블스페이스로 분산시켜서 저장하고 관리하는 기법이 파티션 테이블

- 이 테이블은 데이터의 양이 많을 경우에 주로 사용하는 방법

 

- 오라클에서는 각 버전별로 제공하는 파티셔닝 기법이 다르다.

 

 

- Oracle 11g에서 제공하는 파티셔닝 기법

​

파티셔닝 종류	데이터 분할 기준
Range partitioning	- 연속적인 데이터 입력 시 값의 영역을 지정하여 분할.예) 판매일자
List partitioning	- 일정한 순서가 없을 경우나 특정 값을 기준으로 분할.예) 상품코드
Hash partitioning	- 해쉬 알고리즘 기반으로 랜덤하면서 균등하게 분할.
Composite partitioning      - Range-Range      - Range-List      - Range-Hash      - List-List      - List-Range      - List-hash      - interval-range      - interval-list      - interval-hash	- 위에서 설명된 기법 중 두가지를 복합적으로 구성하여 파티션을 구현함
Interval partitioning	- 동일한 크기의 간격을 기준으로 파티셔닝을 정의함 첫번째 생성되는 파티션을     제외한 모든 파티션은 조건에 맞는 데이터가 입력되는 시점에 자동으로 생성됨.  - Range PARTITIONing 의 확정임
REF partitioning	- 기본키-외래키 관계를 통해 자식 테이블 파티셔닝을 부모 테이블로 부터 상속.  - 파티셔닝 키는 자식 테이블의 실제 컬럼에 저장되지 않음.
Virtual column based   partitioning	- 위에서 설명된 파티셔닝 테크닉중 하나를 사용하되 파티셔닝 키는 가상 컬럼을     기반으로 설정함.  - 가상 컬럼은 디스크에 저장되지 않으며 메타데이터의 형태로만 존재함

 

 

 

(실습은 제외ㅠㅠ)

 

  ① Range PARTITION(범위 파니셔닝)
    - 주로 특정 기준에 의해서 범위를 나눌 때 사용하는 방법, 가장 기본적이고, 많이 가용되는 파티셔닝 기법

       ex) 판매 테이블을 파티셔닝 하는데 판매 날짜를 기준으로 범위를 나누는 경우나
            포털 사이트의 카페 테이블을 파티셔닝 하는데 카페 번호로 범위를 나눌 경우 등에 사용

    - 사용하기 쉽고 관리하기가 쉽다.

    - 데이터가 균일하게 분포되지 못해서 성능저하가 있을 수 있다.

       ex) 년도 별로 판매테이블을 파티셔닝 할 경우 판매가 많이 된 년도에는 데이터가 많아서 작업 속도가 느려질 것이고

             판매량이 적은 년도는 데이터가 적어서 빨리 조회되는 등의 단점이 생김

 

⇒ 이 문제는 데이터가 균등하게 분포되지 못해서 만들어지는 것이며 이러한 문제를 해결하기 위해 등장한 것이

     Hash Partition(해쉬 분할)

 

  ② HASH PARTITION
    - Hash 함수가 데이터를 각 테이블스페이스 별로 균등하게 분포시키기 때문에 데이터가 균등하게 분포되어 성능이 향상됨

    - 데이터를 사용자가 분산하지 않고 Hash 함수가 분산시키기 때문에 데이터의 관리 등이 매우 어려움

 

 

  ③ LIST PARTITION (목록 분할)
    - 파티셔닝 할 항목을 관리자가 직접 지정하는 방식

       ex) 회사 제품 중에 A 제품용 파티션, B 제품용 파티션 , C 제품용 파티션을 각각 생성하는 방식

    - 잘 설정 할 경우 빠른 성능을 보장 할 수 있지만 잘못 설정 될 경우 오히려 성능이 저하 될 수 있음

 

 

  ④ COMPOSITE PARTITION (복합 파티션)

    - 위에서 언급한 여러 가지 파티션을 복합적으로 사용하는 것

       ex) 학생이 많을 경우 학년별로 먼저 나누고 각 학년별로 다시 반으로 나누는 것처럼 티셔닝을 먼저 한 후

            다시 세부적으로 파티셔닝

 

    - 오라클 버전별로 지원되는 형태가 다름

* 8i : range - hash
* 9i : range - list 추가 지원
* 11g : range - range , list - range , list - list , list - hash 추가 지원

 

⑴ Range-Hash

  - 일자별로 먼저 파티셔닝을 하고 그 안에 또 Hash 파티셔닝을 하는 방법

  - 데이터가 어느 파티션에 속할 지 제어할 수 없음

 

⑵ Range-List

  - Range-Hash + 각 로우가 어느 파티션에 속할지 알 수 있음

 

 

  ⑤ Interval PARTITION
    - 11g부터 추가된 기능으로 Range PARTITION 의 확장 형

    - Range PARTITION에서 만약 파티션의 범위(한계)를 벗어난 데이터가 입력이 될 경우

       에러가 발생하면서 입력이 되지 않는데

       이런 문제는 interval PARTITION 을 활용하면 오라클이 필요한 파티션을 스스로 생성한 후 데이터를 입력

    - 이 때 자동으로 생성되는 파티션들은 모두 동일한 범위의 크기를 가지게 되며 파티션 이름은 오라클이 자동으로 지정

 

    - Interval PARTITION 에서 자동으로 파티션을 생성할 때 사용하는 함수

* numtoYMinterval(숫자, '단위') 이렇게 지정

  ex) 1달 단위로 만들어지게 설정하려면 numtoYMinterval(1, 'MONTH') ,
        2달 단위로 만들어지게 하려면 numtoYMinterval(2, 'MONTH'),
        1년 단위로 만들어지게 하려면 numtoYMinterval(1, 'YEAR') 등으로 설정

 

* numtoDSinterval(숫자, '단위')

  - 단위 : DAY , HOUR , MINUTE , SECOND
 ex) 예를 들어 1일 단위로 만들어 지게 하려면 Interval(numtoDSinterval(1, 'DAY')

                     1주일(7일) 단위로 만들어 지게 하려면 Interval(numtoDSinterval(7, 'DAY')

 

  ⑥ SYSTEM PARTITION
    - System PARTITION 은 PARTITION key 를 파티션 생성시에 지정하지 않고 데이터를 삽입할 때 직접 지정하는 방식

    - 검색할 때도 파티션 명을 명시해야만 해당 파티션에서 데이터를찾게 되며

       만약 명시하지 않으면 전체 파티션에서 데이터를 읽게 되어 속도가 급격히 저하 될 수 있음

 

 

 

 (7) 파티션의 인덱스 

 

  :  대량의 데이터가 들어간 파티션 테이블의 데이터를 관리할 때 해당 파티션에 인덱스를 생성 할 수 있음

 

  - 파티션을 생성할 인덱스는 크게 Local Index와 Global Index로 나뉨

 

  - Local Index : 해당 파티션에 대한 정보만 가지고 있는 인덱스

  - Global Index : 모든 파티션에 대한 정보를 전부 가지고 있는 인덱스

 

 

 

 

 

  ① Local Index

  - 파티션 하나당 인덱스 하나가 할당되어 데이터를 관리하는 방식

  - Local Index는 Local Prefixed Index 와 Local Non-Prefixed Index 로 나뉨

  - Local Prefixed Index 는 파티션을 나눌 때 기준이 되는 컬럼으로 인덱스를 생성 한 것이고
     Local Non-Prefixed Index 는 파티션 생성 기준 컬럼 이외의 컬럼으로 인덱스를 생성한 것

  - Local Prefixed Index 는 Unique / Non Unique index 모두 생성할 수 있습니다.

 

SQL> CREATE TABLE panmae   2     ( pdate varchar2(8),   3       pcode number(3),   4       pqty number(5))   5     PARTITION BY RANGE(pdate)        ← pdate 컬럼으로 파티셔닝   6       (PARTITION q1 VALUES LESS THAN ('20100401') TABLESPACE ts_q1,   7        PARTITION q2 VALUES LESS THAN ('20100701') TABLESPACE ts_q2,   8        PARTITION q3 VALUES LESS THAN ('20101001') TABLESPACE ts_q3,   9        PARTITION q4 VALUES LESS THAN ('20110101') TABLESPACE ts_q4, 10        PARTITION q5 VALUES LESS THAN (maxvalue) TABLESPACE ts_q5) ; ​

 

 

 

 

>  Local Index 생성 구문

 

SQL> CREATE INDEX idx_panmae_pdate ON panmae(pdate) LOCAL ; ​

 

 

 

 

  ② Global Index

  - 인덱스와 파티션의 컬럼이나 범위가 다르게 생성

     ex) 예를 들어 판매 일자를 기준으로 파티션 된 테이블이 있다고 가정

           이때 파티션되는 컬럼은 판매일자이지만 주로 조회되는 조건은 제품코드 일 경우 제품코드로 Local Index를

            만들 수도 있고 Global Index를 생성할 수 있습니다.

            그러나 파티션이 판매일자로 나누어져 있기 때문에 각 파티션을 제품코드로 조회 할 경우 성능이 떨어질 수 있습니다.
           그래서 Local Index를 생성할 경우보다는 Global Index를 생성하는 것이 훨씬 효과적입니다.

           즉 테이블은 판매일자를 기준으로 나누어져 있지만 인덱스는 모든 파티션의 제품코드 정보를 모아서

           새로운 파티션 인덱스를 생성

 

  - Global Index 는 하나의 인덱스에 모든 데이터를 다 넣고 관리하는 것보다

    몇 개의 별도의 인덱스 파티션으로 나누는 것이 좋음

 

>  Global Index 생성 구문

 

SQL> CREATE TABLE panmae   2     ( pdate varchar2(8),   3       pcode number(3),   4       pqty number(5))   5     PARTITION BY RANGE(pdate)        ← pdate 컬럼으로 파티셔닝   6       (PARTITION q1 VALUES LESS THAN ('20100401') TABLESPACE ts_q1,   7        PARTITION q2 VALUES LESS THAN ('20100701') TABLESPACE ts_q2,   8        PARTITION q3 VALUES LESS THAN ('20101001') TABLESPACE ts_q3,   9        PARTITION q4 VALUES LESS THAN ('20110101') TABLESPACE ts_q4, 10        PARTITION q5 VALUES LESS THAN (maxvalue) TABLESPACE ts_q5) ;  ​ SQL> CREATE INDEX idx_panmae_pcode ON panmae(pcode)   2       GLOBAL PARTITION BY RANGE (pcode)   3         ( PARTITION p1 VALUES LESS THAN (500) ,   4         PARTITION p2 VALUES LESS THAN (MAXVALUE) ); ​

 

 

  - 일반적으로 Global Index보다는 Local Index를 더 많이 사용하는 이유는 운영이나 유지하는 것이 더 쉽고 효율적이기 때문

 

 

 

 

 

2. 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       경영지원부   1001       재무관리팀 ​

 

 

 

- 장소명을 가지는 LOC 컬럼을 추가

 

SCOTT> ALTER TABLE dept6      2      ADD ( LOC VARCHAR2(10) ); Table altered. SCOTT>SELECT * FROM dept6; ​  DCODE     DNAME         LOC ------- --------- -------   1000       경영지원부   1001       재무관리팀 ​ ​ ​SCOTT>ALTER TABLE dept6      2     ADD ( LOC2 varchar2(10) DEFAULT '서울' ) ; ​ SCOTT>SELECT * FROM dept6; ​  DCODE     DNAME         LOC        LOC2 ------- --------- ------- -------   1000       경영지원부                    서울   1001       재무관리팀​                    서울 ​

 

 

 

 

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

 

SCOTT> ALTER TABLE dept6 RENAME COLUMN LOC2 TO AREA ; ​ SCOTT> RENAME dept6 TO dept7 ;   ​   ← 테이블 이름 변경 시 RENAME ​

 

 

  

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

 

SCOTT> DESC dept7;   Name           Null?                   Type --------  ----------- -------------   DCODE                              VARCHAR2(6)   DNAME        NOT NULL       VARCHAR2(20)   LOC                                  VARCHAR2(10)   AREA                                VARCHAR2(10) SCOTT> ALTER TABLE dept7      2      MODIFY(dcode VARCHAR2(10)) ; Table altered.   SCOTT> DESC dept7;   Name           Null?                   Type --------  ----------- -------------   DCODE                       VARCHAR2(10)   DNAME        NOT NULL       VARCHAR2(20)   LOC                                  VARCHAR2(10)   AREA                                VARCHAR2(10)

 

 

 

 (4) 컬럼 삭제하기

 

SCOTT> ALTER TABLE dept7 DROP COLUMN loc ; DESC dept7;

 

 

- 참조키로 설정되어 있는 부모테이블의 컬럼을 삭제하려 할 경우 에러가 발생하는데 아래와 같은 방법으로 지우면 됨

 

 

SCOTT>ALTER TABLE dept7 DROP COLUMN loc CASCADE CONSTRAINTS ;

 

 

 

 

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

 

  : 어떤 경우에 특정 테이블의 내용을 모두 변경은 할 수 없게 하고 조회만 가능하도록 설정해야 한다면 트리거를 사용하거나

    제약조건을 DISABLE NOVALIDATE 해서 작업을 해야 함

 

  - 읽기 전용인 테이블이기 때문에 데이터 입력이나 테이블 구조 변경은 불가하지만 테이블 자체는 DROP 할 수 있음

 

 

SCOTT> CREATE TABLE t_read      2      ( no NUMBER ,      3         name VARCHAR2(10) ); Table created. SCOTT> INSERT INTO t_read      2       VALUES (1,'AAA'); 1 row created. SCOTT> COMMIT ; Commit complete. SCOTT>SELECT * FROM t_read ;      NO       NAME ------  ----------       1         AAA SCOTT> ALTER TABLE t_read read only ;        ← 읽기전용으로 변경 (Drop은 가능) Table altered.

  

 

- 읽기 전용으로 변경된 테이블에 데이터 입력 시도 SCOTT> INSERT INTO t_read      2      VALUES (2,'BBB') ; INSERT INTO t_read * ERROR at line 1: ORA-12081: update operation not allowed on table "SCOTT"."T_READ"    - 읽기전용으로 변경된 테이블에 컬럼 추가 시도 SCOTT> ALTER TABLE t_read      2      ADD (tel number default 111) ; ALTER TABLE t_read * ERROR at line 1: ORA-12081: update operation not allowed on table "SCOTT"."T_READ" - 읽기 전용인 테이블 삭제 시도 SCOTT> DROP TABLE t_read ; Table dropped.

 

 

- 읽기 전용 테이블을 읽기/쓰기 상태로 변경 ​ ​SCOTT> ALTER TABLE t_read read write ; ​

 

 

 

 

3. TRUNCATE

 

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

 

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

- 해당 테이블에 생성되어 있던 인덱스도 함께 삭제 됨

 

- dept7 테이블의 모든 데이터를 TRUNCATE로 삭제 ​ ​SCOTT> TRUNCATE TABLE dept7 ; ​

 

 

 

4. DROP

 

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

 

 - 테이블 자체가 모두 지워지며 해당 테이블에 생성되어 있던 인덱스 또한 함께 삭제 됨.

 

​ ​SCOTT> DROP TABLE dept7 ; ​

 

 - 오라클 10g부터는 drop table로 삭제할 경우 테이블이 삭제되는 것이 아니라 마치 윈도우에서 파일 삭제시

    휴지통으로 가는 것처럼 휴지통으로 보내지고,

     삭제된 테이블의 이름은 BIN$....로 변경 됨(FLASHBACK TABLE 기능 구현을 위해)

 

 

 

 

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

 

  

 

 

  

 

6. 데이터 딕셔너리(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 는 크게 Static Data Dictionary View와 Dynamic Performance View로 나뉨

 

 - Static Data Dictionary view는 다시 접두어가 DBA_ , ALL_ , USER_ 로 시작되는 3가지로 나뉘게 되고

    Dynamic Performance View 는 접두어가 V$로 시작하는 것들이 대표적

 

 - USER_로 시작하는 딕셔너리는 해당 사용자가 생성한 오브젝트들만 조회 할 수 있음
   ALL_로 시작하는 딕셔너리는 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트

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

 

 -  Static Data Dictionary View는 내용들이 자동으로 변경되지 않고 수동으로 변경작업을 해 주어야 함

 

1) 연습용 테이블 static_table을 생성하고 데이터를 입력   ​SCOTT> CREATE TABLE static_table      2       (no number ) ; Table created. SCOTT> BEGIN      2           FOR i IN 1..1000 LOOP      3                INSERT INTO static_table VALUES (i) ;      4           END LOOP ;      5           COMMIT;      6      END ;      7      / PL/SQL procedure successfully completed.   SCOTT> SELECT COUNT(*) FROM static_table ;   COUNT(*) ----------    1000                  ← 1000 건의 데이터 확인 ​

 

​

2) 데이터 딕셔너리를 조회하여 해당 테이블에 데이터가 몇 건 있는지 확인 ​ ​SCOTT> SELECT NUM_ROWS , BLOCKS      2      FROM USER_TABLES      3      WHERE TABLE_NAME='STATIC_TABLE' ; NUM_ROWS    BLOCKS --------- --------- ​                                              ← 값이 없음 ​

 

 

3) 딕셔너리를 수동으로 업데이트 한 후 다시 조회합니다.   SCOTT>  ANALYZE TABLE static_table COMPUTE STATISTICS ; Table analyzed. SCOTT> SELECT NUM_ROWS , BLOCKS      2      FROM USER_TABLES      3      WHERE TABLE_NAME='STATIC_TABLE' ;  NUM_ROWS    BLOCKS ---------- -------         1000               5           ← 1000건의 데이터와 5개의 블록

 

 

- 위 3번 과정의 ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서

    그 결과를 딕셔너리에 반영시키는 명령어

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.214-279