[PLAYDATA] 데이터 엔지니어링 8월 5주차 8/28

• 8월 28일

요즘 진도가  굉장히 빨리 나가서 daily회고는 쓸 여유도 잘 없는것 같다...

 

 

트랜잭션

트랜잭션이란 더 이상 분할할 수 없는(atomic) 최소 수행 단위이다. 프로시저와 비슷한 개념이다.

-> 즉 트랜잭션은 여러 기본 작업들을 묶어서 하나의 단위 명령으로 만든 개념이다.

-> 트랜잭션 수행 도중 문제발생시 트랜잭션 수행 이전으로 즉시 되돌아간다. (for 안전성)

 

트랜잭션의 구현

트랜잭션은 COMMIT구간으로 이루어진다. 즉 COMMIT과 COMMIT 사이가 하나의 트랜잭션이 된다.

ROLLBACK;은 현재 트랜잭션을 전부 취소하고 최신 COMMIT으로 이동하는 명령어이다.

COMMIT;은 DB를 확정하기 위해 사용하는 명령어이며, 트랜잭션을 완료한다.

(주의!)COMMIT 후에는 ROLLBACK이 불가능하다.

 

SAVEPOINT

트랜잭션 수행 도중(즉 COMMIT명령 실행 전) ROLLBACK을 하려고 할 때, 트랜잭션 시작부분이 아닌 트랜잭션 중간 부분으로 되돌아가고 싶을 때 SAVEPOINT구문을 사용한다.

형식은 다음과 같다.

SAVEPOINT <save_point_name>;

ROLLBACK TO <save_point_name>;

-> 해당 SAVEPOINT로 롤백

예시)

<QUERY>

...

<QUERY>

 

COMMIT;

 

<QUERY>

SAVEPOINT S1;

<QUERY>

SAVEPOINT S2;

<QUERY>

...

<QUERY>

COMMIT;

 

ROLLBACK TO S2;

 

 

세션

세션이란 DB접속부터 접속종료까지의 전체 구간을 의미한다.

-> 하나의 세션은 트랜잭션들의 집합으로 볼 수 있다.

 

 

읽기 일관성

하나의 DB에 여러 세션이 존재할 경우, 한 세션에서 COMMIT을 수행하기 전까지 모든 세션은 최근 COMMIT직후 내용을 보게 되는데 이를 읽기 일관성 이라고 한다.

-> 한 세션에서 COMMIT을 수행하면, 그때서야 수정된 DB의 내용이 다른 세션에게 반영된다.

 

 

LOCK

특정 세션이 특정 트랜잭션을 수행 중에 있을 때, 다른 세션이 동일한 트랜잭션을 다루는 것을 막거나 제어하는 기능이다.

-> 운영체제의 MUTual EXclusion(MUTEX)기능과 매우 유사하다.

LOCK은 두가지 종류로 구분된다.

1. Shared Lock

트랜잭션을 점유하지 않은 세션은 읽기(SELECT)만 가능하다.

2. Exclusive Lock

읽기 명령을 포함하여 모든 작업을 못하게 막는다.

운영체제의 Deadlock처럼 DB의 LOCK기능에서도 Deadlock이 발생할 수 있으니 유의해야 한다.

(주의!) 협업을 할 때, 트랜잭션 업무가 종료되면 꼭 COMMIT이나 ROLLBACK을 해서 다른 사람이 해당 트랜잭션을 사용할 수 있도록 하자.

 

 

데이터 정의어(DDL)

데이터 정의어는 수행할 때마다 자동으로 COMMIT되기 때문에 ROLLBACK이 안된다. 따라서 데이터 정의어에 해당하는 구문을 따로 정리한다.

DDL은 다음 구문들이 있다. 쿼리문에 다음 5가지의 구문 중 하나라도 들어가 있으면 AUTO COMMIT이 된다.

1. CREATE

2. ALTER

3. RENAME

4. TRUNCATE

5. DROP

 

CREATE

CREATE구문은 table을 생성하는 구문으로, 이전에서 보았던 그 CREATE TABLE의 CREATE가 맞다.

CREATE TABLE <account_name>.<table_name>(<column1_name> <datatype1>, <column2_name> <datatype2>, ...);

 

ALTER

테이블의 내용을 변경하는 구문이다. 다음 4가지 하위 구문이 있다.

a. ADD

b. RENAME

c. MODIFY

d. DROP

 

a. ADD

table에 column을 추가한다.

ALTER TABLE <table_name> ADD <column_name> <datatype>;

b. RENAME

table의 column이름을 변경한다.

ALTER TABLE <table_name> RENAME COLUMN <prev_col_name> TO <next_col_name>;

c. MODIFY

table의 column자료형을 변경한다.

ALTER TABLE <table_name> MODIFY <source_column> <datatype_to_change>;

d. DROP

table의 특정 column을 삭제한다. 행을 삭제하는 DELETE구문과 대조되는 부분.

ALTER TABLE <table_name> DROP COLUMN <column_name>;

 

RENAME

table의 이름을 변경한다.

RENAME <prev_table_name> TO <next_table_name>;

(주의!)ALTER TABLE ~ RENAME COLUMN ~;과 혼동 주의.

 

TRUNCATE

테이블의 내용 전체를 삭제한다.

TRUNCATE TABLE <table_name>;

 

DROP

테이블 내용 전체를 삭제하고 테이블마저 삭제한다.

DROP TABLE <table_name>;

 

 

VIEW

뷰(VIEW)란 SELECT 쿼리문을 저장한 오브젝트로, SELECT구문을 통한 table통합을 편리하게 해준다.

CREATE VIEW <view_name> AS (<SELECT QUERY>);

-> CREATE VIEW privilege가 있어야 한다.

뷰 삭제는 다음과 같이 한다.

DROP VIEW <view_name>;

 

 

시퀀스

시퀀스란 ORACLE DB에서 특정 규칙에 맞는 연속적인 숫자를 생성하는 객체를 말한다.

CREATE SEQUENCE <sequence_name>

[INCREMENT BY <n>]

[START WITH <n>]

[MAXVALUE <n> | NOMAXVALUE]

[MINVALUE <n> | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE <n> | NOCACHE];

 

옵션 설명

INCREMENT BY: 번호의 증가 정도이다. Python에서 step이라고 보면 된다. 디폴트는 1.

START WITH: 시퀀스의 시작값이다. 디폴트는 1.

MAXVALUE: 시퀀스의 최대값 지정. 디폴트는 NOMAXVALUE이며, 오름차순이면 10^27, 내림차순이면 -1.

MINVALUE: 시퀀스의 최소값 지정. 디폴트는 NOMINVALUE이며, 오름차운이면 1, 내림차순이면 -10^(28).

CYCLE: 시퀀스의 끝에 도달시 다시 처음부터 번호를 생성할 지 선택

CACHE: 캐싱 기법을 사용하여 속도를 올린다. 기본값은 20. 캐시지정 안하려면 NOCACHE사용

 

시퀀스 사용하기

시퀀스 객체는 특정 정수값을 저장하고 있으며, 쿼리문에 삽입되면 현재 정수값을 반환하거나 +1을 하여 다음 정수값을 반환한다.

<seq_name>.CURRVAL

<seq_name>.NEXTVAL

(주의!) 시퀀스는 값을 내리는 기능을 지원하지 않는다. 즉 한번 올린 값은 다시 내릴 수 없다.

예제) INSERT INTO TB1 VALUES(TB1_SEQ.NEXTVAL, 'A', 20)

 

시퀀스 수정하기

ALTER SEQUENCE <sequence_name>

[INCREMENT BY <n>]

[MAXVALUE <n> | NOMAXVALUE]

[MINVALUE <n> | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE <n> | NOCACHE];

START WITH옵션을 제외하고 그대로 재설정이 가능하다. 형식은 CREATE가 ALTER로 바뀐것 밖에 없다.

 

시퀀스 삭제

DROP SEQUENCE <seq_name>

 

 

제약 조건

제약 조건은 테이블에 저장될 데이터의 조건을 지정한다.

다음과 같은 종류가 있다.

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

5. CHECK

제약 조건은 DB의 데이터 무결성(data integrity)을 실현하는데 사용된다.

 

데이터 무결성

데이터 무결성이란 데이터의 정확성과 일관성을 보장하는 성질입니다.

Domain Integrity: 특정 열에 저장되는 값이 의미적으로 합당한 형식인지를 규정

Entity Integrity: Primary Key는 행을 구별할 수 있는 유일한 데이터이기 때문에 반드시 값을 가지고있어야 하며 NULL, 중복이 불가능하다고 규정

Referential Integrity: 참조 테이블의 Foreign Key값은 참조 테이블의 기본키로서 존재해야 하며 NULL이 가능하다고 규정

 

NOT NULL

table을 생성할 때, 해당 열에 NULL값을 허용하지 않는다.

예제)

CREATE TABLE TB1(COL1 NUMBER(5) NOT NULL, COL2 NUMBER(10));

 

UNIQUE

table을 생성할 때, 해당 열은 중복을 허용하지 않는다. 단 NULL은 허용됨.

예제)

CREATE TABLE TB1(COL1 NUMBER(5) UNIQUE, COL2 NUMBER(10));

 

PRIMARY KEY

해당 열은 중복과 NULL을 허용하지 않는다. (NOT NULL + UNIQUE)

PRIMARY KEY는 하나의 테이블에 하나만 가질 수 있다.

예제)

CREATE TABLE TB1(COL1 NUMBER(5) PRIMARY KEY, COL2 NUMBER(10));

 

FOREIGN KEY

다른 table의 열을 참조하여 존재하는 값만 입력할 수 있다.

 

CHECK

지정한 조건식을 만족하는 데이터만 허용한다.

예제)

CREATE TABLE TB1(COL1 NUMBER(5) CHECK (<condition>), COL2 NUMBER(10));

 

 

사용자 관리

사용자란? DB에 접속하여 데이터를 사용/관리하는 계정(account)을 말한다.

DB Schema란? 데이터를 저장/관리를 위해 자료형, 문법등을 형식언어로 정의한 것

사용자 생성

CREATE USER <user_name> IDENTIFIED BY <password>;

 

사용자 조회

SELECT * FROM ALL_USERS WHERE USERNAME = <user_name>;

(ALL_USERS table과 ALL_USERS.USERNAME column은 시스템 자체적으로 만들어진 자료이다.)

 

사용자 비밀번호 변경하기

ALTER USER <user_name> IDENTIFIED BY <new_password>;

 

사용자 삭제하기

DROP USER <user_name>;

 

 

권한 관리

권한(privilege)을 통해 각 user마다 DB를 다룰 수 있는 행동을 제약할 수 있다.

시스템권한과 객체권한으로 나뉜다.

 

시스템 권한

사용자생성, 정보 수정 및 삭제, DB접근 등 시스템기능에 관한 권한이다.

대표적인 시스템 권한들은 다음과 같다.

CREATE SESSION

CREATE TABLE

CREATE ROLE

CREATE USER

ALTER USER

DROP USER

 

권한 부여 방법은 다음과 같다.

GRANT <system_privilege> TO <user_name | PUBLIC> [WITH ADMIN OPTION];

PUBLIC 구문은 모든 유저를 대상으로 한다는 의미이다.

WITH ADMIN OPTION구문은 해당 유저가 GRANT로 인해 받은 권한을 다른 사용자에게도 부여할 수 있는 권리도 함께 주는 것이다.

권한 회수 방법은 다음과 같다.

REVOKE <system_privilege> FROM <user_name | PUBLIC>;

 

객체 권한

table, view, sequence, procedure, function 등 객체에 관련된 권한이다.

객체 권한들의 목록은 다음과 같다.

ALTER

DELETE

EXECUTE

INDEX

INSERT

SELECT

객체권한들은 각 권한마다 권한이 적용되는 객체 종류가 다르다.

	TABLE	SEQUENCE	VIEW	PROCEDURE
ALTER	O	O
DELETE	O		O
EXECUTE				O
INDEX	O
INSERT	O		O
SELECT	O	O	O

 

객체 권한 부여 방법은 다음과 같다. (기본적으로 ON구문이 추가로 붙음)

GRANT <obj_privilege | ALL PRIVILEGES> ON [<schema>.<obj_name>] TO <user_name | role_name | PUBLIC> [WITH GRANT OPTION];

 

롤(ROLE)

롤(role)이란 여러개의 권한들을 묶은 권한들의 집합이다.

따라서 롤을 사용자에게 부여하면, 롤에 속한 여러 권한들을 한꺼번에 부여할 수 있다.

ROLE생성 -> ROLE에 권한 할당 -> user에게 ROLE부여

(CREATE ROLE) -> (GRANT) -> (GRANT)

(참고) 롤 생성, 수정, 부여, 삭제와 같은 권한 관련 작업은 sysdba, 즉 DB관리자가 수행한다. 따라서 conn /as sysdba로 접속하여 수행해야 롤 관련 다루는 행동들이 권한문제로 거부되지 않는다.

role 생성: CREATE ROLE <role_name>;

(주의!) role을 생성하려는 유저가 CREATE ROLE 권한을 사전에 가지고 있어야 한다.

role에 권한 추가: GRANT <privilege> TO <role_name>;

role을 부여: GRANT <role_name> TO <user_name>;

role에 포함된 권한 확인: SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=<role_name>;

유저로부터 role 회수: REVOKE <role_name> FROM <user_name>;

role 삭제: DROP ROLE <role_name>;