210526 oracle - Transaction, TCL, DDL
강의목표
1. commit, rollback, savepoint 를 이용한 트랜잭션 제어
2. 테이블 생성에 관한 구문을 이해하고 설정할 수 있다.
3. 제약 조건의 특징 및 동작을 이해하고, 적절한 제약 조건을 테이블에 설정할 수 있다.
4. VIEW, SEQUENCE, INDEX, SYNONYM을 생성, 변경 및 삭제할 수 있다.
============================================================================
Transaction이란 논리 작업 단위를 형성하는 DML문의 모음을 말한다.
트랜잭션이란?
- 하나 혹은 두 개 이상의 SQL 문들로 이루어진 작업의 논리적인 단위
- 하나의 트랜잭션 안의 모든 SQL은 동일한 효과로 성공하면 전부 성공하고 실패하면 전부 실패
- 하나의 트랜잭션은 커밋(Commit)될 수도 있고 롤백(Rollback)될 수도 있다.
트랜잭션은 실행 가능한 첫 번째 SQL 문이 실행될 때 시작되어 다음 이벤트가 발생하면 종료된다.
1. COMMIT 또는 ROLLBACK 문이 실행된 경우
2. DCL (Data Control Language : GRANT, REVOKE) 문이 실행된 경우
3. 사용자(Client)가 SQL*Plus를 종료하는 경우
4. 시스템에 장애가 있거나 시스템이 고장 난 경우
5. DDL문 (Data Definition Language : Create TABLE, ALTER, DROP, RENAME)이 실행된 경우
- DDL 실패 시 ORACLE 은 DDL 문(데이터 정의어) 전후에도 암시적 COMMIT을 실행하므로
DDL문이 성공적으로 실행되지 않는 경우에도 서버가 실행한 커밋때문에
이전 명령문은 롤백 할 수 없다.
- 트랜잭션이 종료되면 실행 가능한 다음 SQL 문이 다음 트랜잭션을 자동으로 시작한다.
============================================================================
명시적 Transaction 제어
- COMMIT : 보류중인 모든 데이터의 변경 내용을 영구히 저장하고 현재의 트랜잭션을 종료.
- SAVEPOINT NAME : 현재의 트랜잭션 내에 저장점을 표시
- ROLLBACK : 보류중인 모든 데이터의 변경 내용을 버리고 현재의 트랜잭션을 종료.
- ROLLBACK TO SAVEPOINT NAME : 현재의 트랜잭션을 지정된 저장점으로 ROLLBACK.
암시적 Transaction 제어
- 자동 COMMIT : DDL, DCL 문이 수행되는 경우, iSQL*PLUS가 정상적으로 종료되는 경우
- 자동 ROLLBACK : iSQL*PLUS가 비정상적으로 종료되는 경우,
시스템에 장애가 있을 경우
============================================================================
SAVEPOINT 사용
- 하나의 트랜잭션 내에서 각각의 SQL 문이 실행되었던 시점을 구분하는데 사용된다.
- 아주 긴 작업을 수행하다가 한 번의 실수로 모든 작업을 ROLLBACK 한다면
그 동안 작업 내용까지 모두 취소될 경우가 발생되어 예비책으로 사용된다.
- 트랜잭션 중간 중간에 특정 시점을 표시해두면 실수한 트랜잭션만 취소할 수 있다.
============================================================================
LOCK : 동시에 수행되는 트랜잭션간에 파괴적인 상호작용을 방지한다.
트랜잭션 수행기간 동안 LOCK은 유지된다.
>> 배타적 (exclusive lock) :
DML 문에서 수정하는 각행에 자동으로 배타적 잠금이 수행된다.
트랜잭션이 종료될 때까지 다른 트랜잭션에서 해당 행을 변경하지 못하도록 한다.
이 잠금은 다른 사용자가 동시에 동일한 행을 수정하지 못하도록 하고
아직 완료되지 않은 변경 내용을 다른 사용자가 덮어쓰지 못하도록 한다.
>> 공유 (share lock) :
DML 작업도중 테이블 레벨에서 자동으로 수행된다.
공유 잠금 모드에서는 여러 트랜잭션이 동일한 자원에 대해 공유 잠금을 획득할 수 있다.
==========> DML : 테이블 공유 잠금, 행은 배타적 잠금 수행된다.
잠금은 COMMIT, ROLLBACK 시 자동 해제 된다.
DEADLOCK : 둘 이상의 트랜잭션이 서로 상대방의 LOCK을 순환대기하여
어떤 트랜잭션도 더 이상 진행할 수 없는 상태를 말한다.
오라클은 주기적으로 자동으로 DETECT하여 에러를 리턴한다.
1. 데드락 상태는 두 명 이상의 USER가 서로 LOCK을 걸고 있는 객체에 대기하고 있을 때 발생한다.
2. 현재 세션에 데드락이 발생한 상태
> 다른 세션이 비호환 모드에서 자원(테이블)에 락을 실행할 경우
> 현재 세션이 자원을 요구하는 경우
> 현재 세션에 비해 비호환 모드에서 LOCK 처리된 자원을 다른 세션이 기다리는 경우
오라클 서버는 데드락 상태를 감지한 명령문을 롤백하여 데드락 상태를 자동으로 감지하고 해결한다.
============================================================================
오라클의 오브젝트 종류 : TABLE(데이터저장), INDEX(질의의 효율성),
VIEW(하나이상의 테이블 데이터집합), SEQUENCE(기본 키 값을 생성)
SYNONYM(객체에 다른 이름을 제공)
오브젝트 관련 DDL 명령어 :
CREATE, ALTER, DROP, RENAME, COMMENT,
TRUNCATE( 테이블에 저장되어 있는 모든 행을 삭제할 때 사용 )
TABLE(데이터저장)
SYS 사용자는 모든 데이터 딕셔너리 테이블(사용자 이름, 권한, 객체이름, 테이블 제약조건 등)을 소유.
ex) 사용자가 소유한 테이블 SELECT COLUMN ,,, FROM TABLENAME;
사용자가 소유한 오브젝트 이름과 종류를 확인
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS;
데이터 딕셔너리 뷰
- USER_ : 사용자가 소유하는 객체에 관한 정보
- ALL_ : 사용자가 액세스할 수 있는 모든 테이블에 관한 정보
- DBA_ : DBA ROLE을 할당 받은 사용자만 액세스가 가능
- V$ : 데이터베이스 서버의 성능, 메모리 및 잠금에 대한 동적 성능 뷰
============================================================================
[ 테이블 생성 형식 ]
CREATE TABLE [ schema.]TABLE_NAME (
column datatype [DEFAULT...] ,,,,
)
schema 는 소유자의 이름?
테이블 명은 문자로 시작, 30자까지, 유일한 명칭
DEFAULT 뒤에는 오지 말아야 할 것 : NEXTVAL, CURRVAL, 다른 컬럼명
ex)
CREATE TABLE EMP_TEST(
EMPID NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(7) DEFAULT 100,
HIREDATE DATE DEFAULT SYSDATE
);
DATA TYPE
문자 : CHAR(고정길이 2000), VARCHAR2(가변길이 4000), NCHAR(고정길이 유니코드 2000)
LONG(가변길이 2G)
숫자 : NUMBER[(PREC 전체38, SCALE 소수이하 자리수)] : 가변숫자 21BYTE 최대값
BINARY_FLOAT : 32BIT 부동소수 = 4BYTE
BINARY_DOUBLE : 64BIT 부동소수 = 8BYTE
날짜 : DATE = 7BYTE = BC 4712년 1월 1일부터 9999년도 12월 31일까지
LOB : CLOB : LONG 타입의 확장 형태 / 큰 문자 데이터 저장 / 최대 4G
BLOB : 바이너리로 형태 저장 / 이미지, 영상, 음성 / 최대 4G
NCLOB : NATIONAL CHARACTER SET로 저장 / 최대 4G
BFILE : 외부 운영체제에서 파일시스템에 저장, 테이블에 접근 가능
============================================================================
테이블 변경
- 새로운 컬럼 추가
ALTER TABLE table_name ADD(
column datatype [ DEFAULT expr ] ,,,,
);
기존 컬럼의 수정 : 자리수, 컬럼에 데이터가 없을 경우 타입을 수정
ALTER TABLE table_name MODIFY(
column datatype [ DEFAULT expr ] ,,,,
);
- 컬럼 지정
SET UNUSED column : 컬럼을 삭제할 수 있도록 표시하고 실제 테이블에서 컬럼이 제거되지 않음
SELECT *, DESC 해도 표시되지 않는다.
DROP UNUSED column : UNUSED로 표시된 모든 컬럼을 제거한다.
테이블에서 UNUSED 열로부터 디스크 공간을 회수하려고 사용한다.
ALTER TABLE table_name
SET UNUSED COLUMN column_name;
ALTER TABLE table_name
DROP UNUSED COLUMNS;
ex)
ALTER TABLE BOOK SET UNUSED COLUMN ( AUTHOR );
ALTER TABLE BOOK DROP UNUSED COLUMN;
- 컬럼삭제
기존 컬럼의 삭제 : 반드시 테이블에 하나 이상의 열은 존재해야 한다.
ALTER TABLE table_name DROP(column);
ALTER TABLE table_name DROP COLUMN(column);
ex) ALTER TABLE BOOK DROP (AUTHOR);
TRUNCATE TABLE 문 : 테이블의 모든 행을 제거한다. 저장공간 해제, 롤백할 수 없다.
TRUNCATE TABLE table_name;
============================================================================
ANSI 타입 : 오라클 변환 타입
1. CHARACTER(N) ---------------------------> CHAR(N)
2. CHARACTER VARYING(N) ----------------> VARCHAR(N)
3. NATIONAL CHARACTER(N) --------------> NCHAR(N)
4. NATIONAL CHARACTER VARYING(N) ---> NVARCHAR(N)
5. DECIMAL ----------------------------------> NUMBER
6. INTEGER, INT, SMALLINT ----------------> NUMBER
7. FLOAT, REAL, DOUBLE -------------------> NUMBER