Oracle DB #7 테이블 구조 생성 , 변경, 삭제 하는 DDL

1.테이블 구조 생성 , 변경, 삭제 하는 DDL

1) 테이블 구조를 정의하는 CREATE TABLE 

 

CREATE TABLE table_name

(column_name, data_type expr, …);

 

2) 데이터 형

a. CHAR(size) :고정 길이 문자 데이터. VARCHAR2와 동일한 형태의 자료를 저장할 수 있고, 입력된 자료의 길이와는 상관없이 정해진 길이만큼 저장 영역 차지. 최소 크기는 1

b. VARCHAR2(size) :Up to 2000 Bytes 가변 길이 문자 데이터. 실제 입력된 문자열의 길이만큼 저장 영역을 차지. 최대 크기는 명시해야 하며, 최소 크기는 1

c. NUMBER :Internal Number Format 최고 40자리까지의 숫자를 저장할 수 있습니다. 이때 소수점이나 부호는 길이에 포함되지 않는다.

d. NUMBER(w) :W자리까지의 수치로 최대 38자리까지 가능하다. (38자리가 유효 숫자이다.)

e. NUMBER(w, d) :W는 전체 길이, d는 소수점 이하 자릿수이다. 소수점은 자릿수에 포함되지 않는다.

f. DATE :BC 4712년 1월 1일~AD 4712년 12월 31일까지의 날짜

g. LONG :가변 길이의 문자형 데이터 타입, 최대 크기는 2GB

h. LOB :2GB까지의 가변 길이 바이너리 데이터를 저장시킬 수 있습니다. 이미지 문서, 실행 파일을 저장할 수 있습니다.

i. ROWID :ROWID는 Tree-piece Format을 갖음. ROWID는 DB에 저장되어 있지 않으며, DB Data도 아니다.

j. BFILE :대용량의 바이너리 데이터를 파일 형태로 저장최대 4GB

k. TIMESTAMP(n) :DATE 형의 확장된 형태

l. INTERVAL YEAR TO MONTH :년과 월을 이용하여 기간을 저장

m. INTERVAL DAY TO SECOND :일, 시, 분, 초를 이용하여 기간을 저장두 날짜 값의 정확한 차이를 표현한는데 유용하다.

 

3) 테이블 구조를 변경하는 ALTER TABLE

- ALTER TABLE 명령문은 기존 테이블의 구조를 변경하기 위한 DDL 명령문

- 테이블에 대한 구조 변경은 컬럼의 추가, 삭제, 컬럼의 타입이나 길이를 변경할 때 사용

- 테이블의 구조를 변경하게 되면 기존에 저장되어 있던 데이터에 영향

 

ALTER TABLE로 칼럼 추가, 수정, 삭제하기 위해서는 다음과 같은 명령어를 사용

- ADD COLUMN 절을 사용하여 새로운 칼럼을 추가

- MODIFY COLUMN 절을 사용하여 기존 칼럼을 수정

- DROP COLUMN 절을 사용하여 기존 칼럼을 삭제

 

a. ALTER TABLE ADD

- 새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에 만들어 넣을 수 없음

- 또한 이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이 추가되지만, 컬럼 값은 NULL 값으로 입력

ALTER TABLE table_name

ADD (column_name, data_type expr, …);

 

b. ALTER TABLE MODIFY

- 테이블에 이미 존재하는 컬럼을 변경

- 컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나 크기, 기본 값들을 변경한다는 의미

ALTER TABLE table_name

MODIFY (column_name, data_type expr, …);

 

c. ALTER TABLE

ALTER TABLE table_name

DROP COLUMN column_name;

 

4) SET UNUSED 

- 특정 테이블(EMP02)에서 컬럼(JOB)을 삭제하는 경우 다음과 같이 무조건 삭제하는 것은 위험

- 테이블에 저장된 내용이 많을 경우(몇 만 건에 대한 자료) 해당 테이블에서 컬럼을 삭제하는 데 꽤 오랜 시간이 걸리게 될 것

- 컬럼을 삭제하는 동안에 다른 사용자가 해당 컬럼을 사용하려고 접근하게 되면 지금 현재 테이블이 사용되고 있기 때문에 다른 사용자는 해당 테이블을 이용할 수 없게 됨 이런 경우 작업이 원활하게 진행되지 않고 락(lock)이 발생

- ALTER TABLE 에 SET UNUSED 옵션을 지정하면 컬럼을 삭제하는 것은 아니지만 컬럼의 사용을 논리적으로 제한할 수 있음

- SET UNUSED 옵션은 사용을 논리적으로 제한할 뿐 실제로 컬럼을 삭제하지 않기 때문에 작업 시간이 오래 걸리지 않습니다. 그렇기 때문에 락이 걸리는 일도 일어나지 않게 됨

 

5) 테이블 구조를 삭제하는 DROP TABLE

- 기본 테이블을 제거

DROP TABLE table_name;

 

6) 테이블의 모든 로우를 제거하는 TRUNCATE

- 기존에 사용하더 ㄴ테이블의 모든 로우를 제거하기 위한 명렁어

TRUNCATE table_name;

 

7) 테이블 명을 변경하는 RENAME

RENAME old_name to new_name

 

2. 데이터 딕셔너리와 데이터 딕셔너리 뷰

- 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블을 데이터 딕셔너리라고 함

- 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제 불가능

- 이러한 데이터 딕셔너리를 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호 같은 기호만 보여질 뿐 내용을 알 수 없음

- 데이터 딕셔너리 원 테이블은 직접 조회하기란 거의 불가능

- 의미 있는 자료 조회가 불가능하기에 오라클은 사용자가 이해할 수 있는 데이터를 산출해 줄 수 있도록 하기 위해서 데이터 딕셔너리에서 파생한 데이터딕셔너리 뷰를 제공

접두어	의미
DBA_XXXX	데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회 (DBA는 모두 접근 가능하므로 결국 디비에 있는 모든 객체에 관한 조회)
ALL_XXXX	자신 계정 소유 또는 권한을 부여 받은 객체 등에 관한 정보 조회
USER_XXXX	자신의 계정이 소유한 객체 등에 관한 정보 조회

 

1) USER_ 데이터 딕셔너리

- 접두어로 USER가 붙은 데이터 딕셔너리는 자신의 계정이 소유한 객체 등에 관한 정보를 조회

 

2) ALL_ 데이터 딕셔너리

- ALL_ 데이터 딕셔너리 뷰는 현재 계정이 접근 가능한 객체, 즉 자신 계정의 소유이거나 접근 권한을 부여 받은 타계정의 객체 등을 조회 할 수 있는 데이터 딕셔너리 뷰

- 현재 계정이 접근 가능한 테이블의 정보 조회하는 뷰

- 오라클에서는 타계정의 객체는 원천적으로 접근 불가능, 하지만 그 객체의 소유자가 접근할 수 있도록 권한을 부여하면 타 계정의 객체에도 접근이 가능

 

3) DBA_ 데이터 딕셔너리 뷰

- DBA_ 데이터딕셔너리는 DBA가 접근 가능한 객체 등을 조회 할 수 있는 뷰

- DBA가 접근 불가능한 정보는 없기에 데이터베이스에 있는 모든 객체 등의 의미라 할 수 있음

- USER_ 와 ALL_ 와 달리 DBA_ 데이터딕셔너리뷰는 DBA 시스템 권한을 가진 사용자만 접근할 수 있음