오라클마스터 브론즈(8) 스키마 오브젝트 관리

8-1 스키마

 

스키마란, 어떤 유저가 소유하는 테이블 등의 오브젝트를 저장하는 상자와 같은 것이다. 유저를 작성하면 유저이름과 유저의 스키마가 자동적으로 작성된다.

만약 HR이라는 유저가 [departments] [employees] [job]라는 테이블을 작성하면, 그 테이블의 소유자는 HR유저가 된다. 

 

 

 

HR 유저는, OE 유저가 소유하는 customer 테이블에 대한 조회 권한이 없다면, customer 테이블을 읽어들일 수 없다. 그리고, 동일 스키마 안에는 같은 이름의 테이블을 지정할 수 없다.

 

 

 

스키마 오브젝트와 비스키마 오브젝트

스키마 오브젝트: 테이블이나 인덱스 등

비스키마 오브젝트: 유저나 롤 등, 스키마 오브젝트가 아닌 오브젝트

 

 

 

 

8-2 데이터형식, 제약과 테이블의 작성

 

Oracle에서 지정 가능한 주요 데이터 형식

데이터형식	저장할 수 있는 데이터
NUMBER	정수 및 소수
CHAR(n)	사이즈가 n바이트인 고정길이의 문자형. n은 1에서 2000까지 가능하며, n바이트보다 적은 사이즈의 문자열이 입력되면, 부족분은 공백문자로 채워진다.
VARCHAR2(n)	최대 사이즈가 n바이트인 가변길이 문자형. n은 1에서 4000까지 가능하다.
BLOB	최대 128 테라바이트의 바이너리 데이터
CLOB	최대 128 테라바이트의 문자열
DATE	소수초를 포함하지 않는 날짜와 시각
TIMESTAMP	소수초를 포함하는 날짜와 시각

 

NUMBER(n,m): 최대 유효 자릿수는 n, 소수점 이하의 최대 자릿수는 m인 소수 혹은 정수. 예를 들어 NUMBER(7,2)인 경우에는, 12345.67 을 입력할 수 있다.

BLOB: 사진이나 음석 등의 바이너리 데이터를 저장하기 위한 데이터 형식이다. 

CLOB: CHAR형식이나 VARCHAR2형식에는 대응 불가능한 긴 텍스트 문자열을 저장하기 위한 데이터 형식이다.

 

 

 

테이블에 정의 가능한 제약(CONSTRAINT)

제약	의미	기능
NOT NULL	-NOT NULL 제약	컬럼에 NULL가 설정되는 것을 금지
UNIQUE	-유일성 제약	컬럼에 중복값이 설정되는 것을 금지 (NULL의 입력까지 금지하는 것은 아니다.)
PRIMARY KEY	-기본키 제약	컬럼(혹은 컬럼의 집합)에 중복값이나 NULL이 설정되는 것을 금지
FOREIGN KEY	-외부키 제약 -참조정합성제약	컬럼(혹은 컬럼의 집합)의 값이 관련 테이블의 유일키 혹은 기본키의 값을 일치하는 것을 보증함
CHECK	-체크 제약	컬럼(혹은 컬럼의 집합)에 지정된 체크 조건을 만족하지 않는 값을 입력하는 것을 금지

 

(* 참조 정합성: 데이터 정합성의 종류 중 하나로, 참조하는 테이블은 참조되는 테이블의 기본키 컬럼을 참조해야 한다. 그래야 참조하는 테이블은 참조되는 테이블의 유일한 데이터를 가지고 참조 작업을 할 수 있기 때문이다.)

 

 

 

 

테이블의 작성

CREATE TABLE products(
	prod_id 	NUMBER       CONSTRAINTS PRODUCTS_PK   PRIMARY KEY,
    prod_name 	VARCHAR2(20) CONSTRAINTS PRODUCTS_UK   UNIQUE,
    prod_price  NUMBER       CONSTRAINTS PRODUCTS_CHK1 CHECK (prod_price >=0));

 

 

 

 

8-3 테이블의 삭제와 쓰레기통

 

테이블의 삭제

DROP TABLE <테이블이름>;

쓰레기통에서 플래시백 드롭 하기

 - Oracle에는 '쓰레기장'이라는 개념이 있어서, drop완료한 테이블을 복원하는 기능이 있다. 단, drop 처리를 할 때 purge를 지정했다면 그 테이블은 플래시백 드롭 처리도 할 수 없다. 그리고, 플래시백 드롭 기능을 사용하기 위해서는 RECYCLEBIN 초기화 파라미터를 'ON'으로 지정해 놓아야 한다.

FLASHBACK TABLE <테이블이름> TO BEFORE DROP;

RECYCLEBIN 초기화 파라미터는 디폴트가 on이다.

 

 

 

 

8-4 인덱스와 뷰

 

인덱스: 테이블의 컬럼 혹은 컬럼의 집합의 핀포인트를 모아 놓은 오브젝트로써, 쿼리의 검색조건에 따라 데이터를 고속으로 읽을 수 있다.

 인덱스를 통한 조회는, 컬럼값과 함께 ROWID를 보관하는 인덱스의 컬럼값을 찾아간 후 컬럼값과 함께 적혀진 ROWID의 주소(A번째 데이터 파일의 B번째 블록의 C번째 데이터인지에 관한 주소를 담는다.)를 읽고 테이블을 찾아가는 원리로 진행된다. 참고로 이 ROWID는 데이터파일의 주소를 적어놓은 것으로, 데이터파일과 물리적으로 연결된 주소가 아니다. 논리적인 주소이다. 

 

(*작성자 생각: 만약 ROWID가 물리적으로 연결된 주소였다면, 인덱스에서 ROWID를 읽은 후 굳이 테이블을 찾아가지 않았겠지???)

 

 

 

뷰: 뷰는 하나 혹은 여러 테이블에 대한 SELECT문으로 정의된 스키마 오브젝트이다. 뷰는 시큐리티를 위해서지, 조회 성능을 향상하기 위한 방법이 아니다. 뷰는 항상 실제 테이블을 참조하기 때문이다. 

 

  - 뷰를 이용한 데이터 보호 효과

일반 사용자에게는 실제 테이블을 select하지 않고, view만을 참조 가능하게 함으로써 민감한 정보(예를 들어, sal과 같은..)를 가릴 수 있다.

 

- 뷰에는 DML이 가능하며, 뷰를 변경하면 실제 테이블도 갱신된다. READ ONLY 뷰로 변경할 수 있다.