5. DDL문장과 딕셔너리

[기타SQL]/oracle SQL

5. DDL문장과 딕셔너리

JJ* 2014. 8. 27. 22:09

5장.DDL명령과 딕셔너리

DDL : 통을 생성하고 관리하는 명령

DML : 통안의 내용물 생성하고 관리하는 명령

DDL(Data Manipulation Language) : INSERT( 입력), UPDATE(변경), DELETE(삭제), MERGE(병합)

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

DCL(Data Control Language) : GRANT(권한주기), REVOKE(권한뺏기)

TCL(Transaction Control Language) : COMMINT(확정), ROLLBACK(취소)

SELECT : 어떤 분류에서는 DQL(Data Query Language)라고 합니다.

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

1) 일반 테이블 생성

: no 숫자3byte , birth는 date날짜 , default sysdate : 입력하지않으면 기본값인 현재날짜를 적겠다.

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

:number(3,1) 숫자3글자인데 소수점아래는1글자, 입력하지않으면 기본으로 0 입력

:NAME에 문자들어오는데, 입력하지않으면 기본으로 NO입력

:HIREDATE는 날짜 들어오는데, 입력하지않으면 현재날짜입력.

3) 한글 이름으로 테이블 생성가능(권장하지않음)

*테이블 생성시 제한 사항.

- 반드시 문자로 시작해야 하며, 숫자로 시작할 수는 없다.

- 테이블 이름이나 칼럼 이름은 최대 30byte까지 가능.

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

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

4) 임시테이블 생성하기(Global TEMPORARY TABLE)

: 테이블을 생성하면 딕셔너리에 저장되어 있다가 사용자가 해당 테이블에 엑세스하면 메모리 상에 해당 테이블을 만들고 데이터를 가져옴. 사라지는 시점은 트랜잭션이 끝나거나 세션이 끝나는 시점이다

. 기본적으로 세션별로 만들어지므로 다른 세션에서 테이블을 공유할 수 없다. 즉 같은 계정이어도 다른 터미널에서는 조회할 수 없다.

- on commit preserve rows : 세션이 종료해야 데이터 사라짐.

- on commit delete rows : commit 치면 데이터 사라짐.

: 생성되어 있는 Temporary Table 조회하기.

5) 테이블 복사하기(CTAS 씨타스)

(1) 모든 칼럼 다 복사하기

(2) 특정 칼럼만 복사하기.

(3) 테이블의 구조(칼럼)만 가져오고 데이터 안 가져오기 : where절에 틀린 조건을 적어준다.

6) 가상칼럼테이블생성하기. (11g부터 추가된 기능) - 나온지 얼마 안돼서 검증이 안됨. 왠만하면 쓰지 말 것.

(... 이하 생략 ...)

2. ALTER 명령 (오라클의 양악과 같은 존재! 사용량이 많은 시간에 수행하면 아주 위험)

1) 새로운 칼럼 추가

-----------------------------------------------------------------------------------

- DEFAULT : NULL값인경우 '서울'이 입력되도록.

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

-칼럼명 변경 ex. Dept6 테이블의 LOC2칼럼을 AREA로 이름 변경

[문법] : alter table 테이블명 rename column 바꾸기전칼럼명 to 바꾼후칼럼명;

-테이블명 변경 ex. Dept6 테이블을 dept7로 이름 변경

[문법] : rename 바꾸기전테이블명 to 바꾼후테이블명;

3)칼럼의 데이터 크기 변경

[문법] : alter table 테이블명

modify(칼럼명 varchar2(데이터크기));

4)칼럼 삭제하기

[문법] : alter table 테이블명 drop column 칼럼명;

- 만약, 참조키로 설정되어 있는 부모테이블의 칼럼을 삭제할 경우,

[문법] : alter table 테이블명 drop column 칼럼명 cascade constraints;

5) 읽기 전용 테이블로 변경(11g new feature)

- ALTER TABLE 테이블명 read only

- 다시 읽기, 쓰기 모드로 변경 : ALTER TABLE 테이블명 read write

- 읽기 전용인 테이블을 삭제할 때 : DROP TABLE 테이블명;

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

[문법] : TRUNCATE TABLE 테이블명

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

[문법] : DROP TABLE 테이블명

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

DELETE : 데이터는 지워지지만, 그 공간은 그대로 남아있어서 용량은 줄어들지 않는다. 원하는 데이터만 지울 수 있다.

TRUNCATE : 데이터지워지면서 용량도 줄어든다. 원하는 데이터만 지울 수 없다.

DROP : 테이블 자체를 삭제한다.

6. 데이터 딕셔너리 (Dictionary)★★★(면접) : (사람의 두뇌와도 같다.) 데이터베이스를 운영하기 위한 정보들을 모두 특정한 곳에 모아두고 관리하는 곳. (많이 쓰는 것 위주로 활용법 외워주는게 좋아. 잘 알수록 연봉이 높아져)

명령어를 쳤을 때 딕셔너리에 가서 확인해보고 맞으면 데이터 꺼내오고, 틀리면 에러를 띄운다. 딕셔너리가 나가버리면 절대 못 고친다. 딕셔너리는 한 개만 있는게 아니라 10g는 1860개, 11g는 2600개 정도가 있다. 에러가 났을 때 딕셔너리에 찾아보면 다 알 수 있다.

* Base Table : 데이터베이스를 생성하는 시점에 자동으로 만들어 진다. 원본이기 때문에 DBA라 할지라도 접근을 못하게 막는다. 서버프로세스만 접근할 수 있다.

* Data Dictionary View

- Static Data Dictionary view

(User_) : 자기소유의 테이블만 볼 수 있다.

(All_) : 자기소유+접근가능한 테이블을 볼 수 있다. (전부 다 볼 수 있다는 의미가 아니야.)

(DBA_) : 전부 다 볼 수 있다. (DBA권한을 가진 사람만 볼 수 있다.)

- Dynamic performance view

(v$) : (자동으로 업데이트- 정말 중요한 일부만 v$로 지정한다.)

ex)) Scott계정이 A,B 테이블만들고, HR계정이 C,D테이블을 만들었을때, HR이 Scott이 C를 select할 수 있도록 허락해주면, scott이 C도 볼 수 있다. scott계정으로 로그인해서 select * from user_tables치면 A,B만 볼 수 있다. select * from all_tables치면 A,B,C도 볼 수 있다. select * from dba_tables치면 A,B,C,D를 다 볼 수 있다.

>>옵티마이져가 딕셔너리들을 보고 방향을 지정해준다.

>> table analyzed : (딕셔너리 수동으로 업데이트) analyze 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어로, 절대로 사용량 많은 업무시간에는 작업을 피해야 한다.

실제로 table에는 데이터가 1000건이 들어 있는데, static이면 딕셔너리는 내용이변경이안되어 값이 안뜸.

1) 연습용 테이블 static_table 을 생성하고 데이터를 입력합니다.

SCOTT>CREATE TABLE static_table
   2 (no number ) ;

Table created.

SCOTT>BEGIN                                      : (PL/SQL) 실습을 위해서 1000 건의 입력작업을 반복하기 위해 사용.
   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
-------------    ----------
                                          <- 이 부분에 값이 없음.

: 딕셔너리인 USER_TABLES를 조회하니 static_table 에 데이터가 한 건도 없는 것으로 조회됨.

실제 데이터는 1000 건 있지만 딕셔너리 내용이 변경이 안되어서 딕셔너리는 이 사실을 모르고 있는 것.

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

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

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

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

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

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

10. 데이터 딕셔너리 종류와 특징을 간단하게 쓰세요.

저작자표시 변경금지