권한
특정한 SQL문을 수행할 수 있는 권리. 권한은 2가지로 나누어 진다.
- 시스템권한: 오라클 DBMS에 영향을 줄 수 있는 권한
- 객채권한: 객체에 영향을 줄 수 있는 권한
권한 확인 방법(USER)
내가 받은 시스템 권한을 확인할 수 있는 방법
1.
SELECT * FROM session_privs; --시스템권한을 확인(아래 2개를 UNION해서 보여줌)
--참고: CREATE SESSION는 DB에 접속할 수 있는 권한
2.
SELECT * FROM user_sys_privs; --DBA부터 직접 부여받은 시스템권한
3.
SELECT * FROM session_roles; --내가 받은 롤에 대한 정보 확인
SELECT * FROM role_sys_privs; --롤 안에 들어 있는 시스템권한 확인
--Role이란, 개별 권한을 매번 부여하기 귀찮으니 여러 권한을 묶어서 권한을 부여함
오브젝트 권한을 확인하는 방법 (USER)
SELECT * FROM user_tab_privs;
--내가 받은 객체권한과 내가 부여한 개체권한에 대한 정보 확인
SELECT * FROM role_tab_privs; --내가 받은 롤 안에 들어있는 객체 권한의 정보를 확인
권한을 확인하는 방법 (DBA)
SELECT * FROM dba_sys_privs;
--시스템 권한을 어떤 유저한테 부여했는지에 대한 정보
SELECT * FROM dba_tab_privs;
--객체권한을 어떤 유저한테 부여했는지에 대한 정보
SELECT * FROM dba_roles;
--DB에 롤에 대한 정보
SELECT * FROM dba_role_privs;
-- 롤을 어떤 유저한테 부여했는지 정보
SELECT * FROM dba_users;
--DB에 생성되어 있는 유저 정보 확인
SELECT * FROM dba_tablespaces;
--tablespace 정보를 확인
SELECT * FROM dba_data_files;
--물리적인 저장 공간 정보 확인
DBA의 필수 tablespaces
- SYSTEM: 여러 유저 정보 등을 저장해놓은 곳
- SYSAUX: SYSTEM을 보조하는 역할(튜닝 시 사용)
- UNDO- : 휴지통 같은 기능
- TEMP :예를들어 sort작업 시 임시 공간이 필요할 때 씀
하지만 SQL을 다룰 때 세부 경로를 찾아서 확인했나? 아니다. DBMS의 장점 중 하나가 파일 경로를 자동으로 찾아서 편리하게 이용할 수 있게 한다.
참고- DATA 저장 단계
- Oracle DB 안에 DATA를 저장하는 단계는 다음 순서와 같다
| DB | 논리적인 영역 |
|---|---|
| TABLESPACE | DB보다 하위의 논리적인 영역 |
| SEGMENT | Storage를 갖는 TABLE(OBJECT) 실제 데이터가 저장되는 영역 |
| EXTENT | BLOCK의 집합 |
| BLOCK | 최소 INPUT/OUTPUT 단위 크기는 보통 2K, 4K, 8K, 16K, 32K |
- DB와 OS 간의 관계
| DATABASE(논리적) | OS (물리적) |
|---|---|
| TABLESPACE | data file |
| SEGMENT | 저장 공간이 필요한 오브젝트(ex. table, index) |
| EXTENT | 일정한 수의 연속된 block 공간을 지칭 |
| BLOCK | OS Block(대부분 512 byte 사용) |
DB에서 BLOCK까지 전부 1대 다 관계로 이루어져있다. 또한 DB와 OS(tablespace와 data file 등)은 1:다 관계이다. (파일 하나당 저장 공간이 한정되어 있기 때문)
유저 만들기
지금까지 HR 계정으로 접속해서 SQL을 배웠다. 이제 새로운 유저를 생성해서 작업해보자.
CREATE (생성)
USER 생성 권한이 있는 DBA USER를 생성할 수 있다. olap이란 user를 생성해보자.
CREATE USER olap --ID
IDENTIFIED BY oracle --PW, 여기까지가 user생성시 필수문장
DEFAULT TABLESPACE users
-- 미수행시 system으로 저장됌 (system은 dictionary가 저장된 곳이라 꼭 다른 tablespace에 할당해주자)
temporary tablespace temp
QUOTA 10m ON users; --10m만큼 quota 부여 (quota unlimited on 은 무제한)
SELECT * FROM dba_users; --생성되었는지 확인
SELECT * FROM dba_ts_quotas; --쿼터가 부여된 공간을 확인 (-1은 unlimited)
ALTER (수정)
만약 유저이름이나 패스워드, QUOTA 등의 값을 바꾸고 싶다면 ALTER 를 쓰자
ALTER user 바꿀 유저이름
ALTER identified by 바꿀 비밀번호
ALTER quota 바꿀 값
GRANT (권한 부여)
olap 유저는 생성은 되었지만 권한이 부여되지 않아 접속이 되지 않는다. GRANT를 사용하여 접속 권한을 부여해야 한다.
...
GRANT CREATE session to olap; --권한 부여하는 방법
--GRANT 는 권한부여 시 사용
SELECT * FROM dba_sys_privs WHERE grantee = 'OLAP'; --DBA가 olap에게 준 권한 확인 방법
--DICTIONARY 정보에는 대문자로 저장되어서 대문자로 유저이름(OLAP)을 검색
SELECT * FROM session_privs; --자기가 받은 권한을 확인(olap 계정에 접속해서 확인)
SELECT * FROM user_sys_privs --DBA로부터 받은 권한 확인
REVOKE (권한 회수)
부여한 권한은 REVOKE를 사용하여 회수 할 수 있다.
...
REVOKE CREATE session FROM olap; --권한을 회수하는 방법 (DBA에서 수행)
SELECT * FROM dba_sys_privs WHERE grantee = 'OLAP'; --생성된 olap 유저가 없어졌다.
TABLE 생성시 필요한 권한
table을 생성하려면 2가지 권한이 필요하다.
- 시스템권한 (CREATE TABLE)
- 확인 방법:
SELECT * FROM session_privs;
- 확인 방법:
- tablespace를 사용할 수 있는 권한 (저장공간)
- 확인 방법:
SELECT * FROM user_ts_quotas;
- 확인 방법:
테이블 생성 규칙
데이터를 저장하는 object는 table이다. 테이블 이름, 컬럼의 이름, 유저 이름, object 이름은 규칙을 가진다.
- 문자로 시작
- 길이는 1 ~ 30자까지 가능
- 영문자, 숫자, _, #, $ 를 사용
- 동일한 유저가 소유한 다른 객체의 이름과 중복이 안된다.
- 오라클 서버의 예약어는 사용할 수 없다. (Ex. select, sysdate 등)
컬럼의 데이터 유형
- varchar2(4000): 가변길이 문자데이터. 문자 데이터 최소값 1 최대값 4000
- char: 고정길이 문자데이터. 최소값(기본값) 1 최대값 2000
- number(p,s): 가변숫자데이터. 숫자 전체 자릿수가 p이고 소수점 이하 자릿수가 s. p의 자릿수는 1 ~ 38
- date: 날짜 데이터 타입.
- long: 가변 문자 데이터 타입. 최대 2G (clob으로 대체됌)
- clob: 가변문자 데이터. 최대 4G
- blob: 이진데이터. 최대 4G. 이미지 데이터를 저장할 때 사용(bfile을 많이 사용)
- bfile: 외부파일에 저장된 이진데이터. 최대 4G
참고
데이터의 유형은 character set을 무엇으로 쓰는지에 영향을 받는다. character set을 확인하는 쿼리는 다음과 같다.
SELECT * FROM nls_database_parameters;
--NLS_CHARACTERSET와 NLS_NCHAR_CHARACTERSET을 확인
NLS_CHARACTERSET의 종류 (OS에 영향을 받음)AL32UTF8: 유니코드, 전세계 출판되는 글자는 입력가능 (한글 3BYTE)KO16MSWIN949: 한글, 영어, 한자, 일어, 한글 입력 가능, char, varchar, long, clob의 영향을 줌 (한글 2BYTE)
NLS_NCHAR_CHARACTERSET(default값으로 설정되어 있음)AL16UTF16: 유니코드, nchar nvarchar2 nclob에 영향을 줌
NLS_NCHAR_CHARACTERSET이 나온 이유는 자국 character set을 사용하지만 그 외 간간히 나오는 외국문자의 character set 때문에 그 경우에는 utf16으로 사용한다.
olap 유저에서 확인 결과, tablespace는 사용할 수 있는 권한이 있지만 시스템 권한이 없다. DBA에 접속하여 권한을 부여해보자.
...
GRANT CREATE TABLE to olap; --테이블 생성 권한 부여
SELECT * FROM dba_sys_privs where grantee = 'OLAP'; --DBA에서 권한 부여 확인
SELECT * FROM session_privs; --olap에서 권한 부여 확인
권한 부여 후 테이블을 생성해보자. 테이블을 생성할 때는 tablespace를 쓰는 습관을 가지자.
...
CREATE TABLE test(id number); --default tablespace에 저장됌, 이러지말
CREATE TABLE test1(id number) tablespace users; --users tablespace에 저장.
SELECT * FROM user_tables; --테이블 확인 방법
DROP (테이블 삭제)
필요없는 테이블을 만들었으니 이제 지우자
...
DROP TABLE test purge; --test 테이블 삭제
DROP TABLE test1 purge; --test1 테이블 삭제
purge 옵션이 없을 경우 휴지통에 버려지지만, purge를 사용 시 영구히 삭제된다.
이제 emp테이블을 만들어 신규 데이터를 넣어보자. 먼저 테이블을 만들자.
...
CREATE TABLE emp(id number(4), name varchar2(20), day date)
tablespace users;
SELECT * FROM emp;
테이블 생성시 default 값을 넣을 수 있다.
CREATE TABLE emp(id number(4), name varchar2(20), day date default sysdate)
tablespace users;
INSERT (ROW 입력)
INSERT: ROW를 저장하는 명령문
INSERT 절에 대응되게 데이터를 입력해서 넣어야 한다. (신규 row는 하나씩 밖에 저장을 못한다)
...
desc emp --테이블의 구조를 먼저 확인하자
INSERT INTO emp(id,name,day)
VALUES(1,'홍길동',to_date('2018-06-11','yyyy-mm-dd'));
SELECT * FROM emp;
ROLLBACK (행 생성 취소)
하지만, INSERT는 했지만 영구히 저장되지 않았다.(Transaction 단위, 실행한 창에서만 확인할 수 있음) 그래서 ROLLBACK을 써서 취소를 할 수 있다. 이를 읽기 일관성이 좋다고 할 수 있다.
...
ROLLBACK;
SELECT * FROM emp;
COMMIT (영구히 데이터 저장)
COMMIT; 을 써서 데이터를 영구히 저장해보자
...
INSERT INTO emp(id,name,day)
VALUES(1,'홍길동',to_date('2018-06-11','yyyy-mm-dd'));
COMMIT;
SELECT * FROM emp;
ROLLBACK;
SELECT * FROM emp; --rollback을 해도 영구히 저장됐기 때문에 사라지지 않음
NULL 입력
데이터에 NULL 값을 넣는 방법은 2가지가 있다. 해당 컬럼에 NULL을 입력하거나 컬럼과 데이터를 입력하지 않을 때이다.
...
INSERT INTO emp(id,name,day) --컬럼리스트를 다 쓰는 걸 지향하자.
VALUES(3,'박지성',NULL);
INSERT INTO emp(id,name)
VALUES(4,'차두리');
--첫번째쿼리를 사용하자
유저 삭제
DBA 권한으로 들어가서 DROP으로 유저 삭제가 가능하다.
...
DROP USER olap;
>SQL error: ORA-01940: cannot drop a user that is currently connected
유저가 접속 중이거나, OBJECT가 생성되어 있으면 바로 지울 수가 없다. 우선 접속한 SESSION을 KILL하자.
SESSION KILL 방법
SID값과 SERIAL#값으로 KILL 할 수 있다.
...
SELECT * FROM v$session WHERE username = 'OLAP';
--SID값과 SERIAL값 찾는 문장
ALTER SYSTEM KILL SESSION 'SID값,SERIAL값' IMMEDIATE;
--SESSION KILL
DROP USER
...
DROP USER olap;
>SQL ERROR: ORA-01922: CASCADE must be specified to drop 'OLAP'
SESSION 은 끊었지만 OBJECT가 생성되어 있어서 삭제를 못한다. CASCADE를 사용하여 지우자.
DROP USER olap CASCADE;
SELECT * FROM dba_users; --DB에 저장되어 있는 USER정보 확인
지워졌음을 확인했다!
데이터 복제
위에서 만든 emp에 있는 5개의 row를 새로운 테이블을 만들어 복제를 시켜보자. (서브쿼리 사용)
...
CREATE TABLE emp_new(id number(4), name varchar2(20), day date)
tablespace users;
--새로운 테이블 생성
INSERT INTO emp_new(id, name, day)
SELECT * FROM emp;
COMMIT;
--5개의 행을 emp_new테이블에 복제함
테이블 구조와 데이터 복제
emp_new1의 새로운 테이블을 만들면서, 테이블 구조와 데이터를 같이 복제를 해보자.
...
CREATE TABLE emp_new1
AS SELECT * FROM emp;
--테이블 구조와 데이터를 함께 복제
테이블 구조만 복제
테이블을 생성하면서 데이터를 복제하면 자동 COMMIT 이 된다. 테이블을 새로 만들며, 테이블의 구조만 복제시켜보자.
...
CREATE TABLE emp_new2
AS SELECT * FROM emp WHERE 1=2;
--WHERE절에 FALSE 조건을 넣어 데이터를 포함시키지 않는다.
데이터/컬럼 수정
UPDATE (필드값 수정)
>Syntax
UPDATE 테이블명
SET 바꿀 데이터 조건
WHERE 수정할 데이터 조건
UPDATE를 사용하여 필드단위로 수정이 가능하다.
/*
INSERT INTO emp(id, name, day)
VALUES(1,'홍길동',TO_DATE('2018-06-10','yyyy-mm-dd'));
COMMIT;
INSERT INTO emp(id,name,day)
VALUES(2,'박찬호',default);
INSERT INTO emp(id,name)
VALUES(3,'박지성');
INSERT INTO emp(id,name,day)
VALUES(4,'차두리',NULL);
INSERT INTO emp(id,name,day)
VALUES(5,default,default);
COMMIT;
*/
UPDATE emp
SET day = sysdate - 10; --행을 제한하지 않으면 전체 행에 영향을 줌
ROLLBACK;
UPDATE emp
SET name = '손흥민'
WHERE id = 5;
UPDATE emp
SET name = '이영표', day = sysdate - 10 --여러행 수정시, 콤마로 연결
WHERE id = 4;
COMMIT;
DELETE (행 삭제)
DELETE로 행 단위로 삭제가 가능하다.
...
DELETE FROM emp; --모든 행이 삭제 됌
ROLLBACK;
DELETE FROM emp
WHERE id = 5;
ALTER TABLE ADD (컬럼 추가)
새롭게 생성하는 열은 무조건 마지막 열에 추가 된다. 컬럼을 제어하려면 새롭게 테이블을 만들어야 하니 자주사용하는 컬럼은 앞에 배치하도록 하자.
/*
CREATE TABLE emp
AS SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE 1=2;
*/
ALTER TABLE emp ADD job_id varchar2(10);
ALTER TABLE MODIFY (컬럼 수정)
수정 가능 여부는 데이터에 종속된다. 데이터가 없을 시에는 데이터 타입을 바꿀 수 있지만 데이터가 들어있으면 타입을 바꿀 수 없다.
...
ALTER TABLE emp MODIFY job_id varchar2(20);
ALTER TABLE DROP/SET UNUSED (컬럼 삭제)
ALTER는 DDL문이기에 Transaction이 발생하지 않는다. 그러니 컬럼 삭제 시에는 주의해서 해야한다.
...
ALTER TABLE emp DROP COLUMN job_id;
DROP COLUMN이 실행 중에는 해당 테이블을 사용하지 못한다. 컬럼에 데이터가 많으면 처리 시간이 길어질 수 있다. 그래서 SET UNUSED로 사용을 막아 놓고 비작업시에 지운다.
ALTER TABLE emp SET UNUSED(salary); --SALARY 컬럼을 UNUSED 시킴
SELECT * FROM user_unused_col_tabs;
--table에서 unused 한 갯수 정보만 줌 (컬럼 이름은 확인 불가)
ALTER TABLE emp DROP UNUSED COLUMNS; --UNUSED 컬럼을 삭제
TRANSACTION (읽기 일관성 기능)
논리적으로 DML(INSERT/UPDATE/DELETE/MERGE)을 하나로 묶어서 처리하는 작업 단위. DML문을 실행 시 Transaction이 발생한다.
INSERT ---- Transaction 시작
SAVEPOINT a;
UPDATE ----
SAVEPOINT b;
DELETE ----
ROLLBACK TO b; --b 이후의 DML문이 취소됌
COMMIT; --b 이전의 작업을 영구히 저장
AUTO COMMIT 발생하는 경우
- DDL, DCL문 수행 시
- 정상적인 종료를 할 때 (exit)
- 다른 세션으로 connect를 수행할 때
AUTO ROLLBACK 이 발생하는 경우
- 비정상적인 종료를 할 때 (창닫기, 전원공급 중단, 네트워크 장애, 컴퓨터 종료 등)
독립 TRANSACTION 처리
PL/SQL 참고(호출단위 프로그램에서 TCL문을 수행할 때 독립적으로 TRANSACTION을 처리 할 수 있다.)
OBJECT 권한 및 데이터 이관
HR계정의 employees 테이블을 사용하려 한다.
SELECT * FROM hr.employees;
--hr의 employees를 확인하려는데 오류 발생
SELECT * FROM user_tab_privs; --부여받은 오브젝트 권한 확인
--아! 객체권한이 없구나
HR 계정으로 들어가서 권한을 부여해줘야 한다.
...
GRANT SELECT ON hr.employees TO ora10;
--employees테이블을 SELECT 라는 권한을 ora10에게 준다
이제 ora10에서 원하는 데이터를 추출해서 테이블에 넣어보자
...
INSERT INTO emp(id,name,day)
SELECT employee_id, last_name, hire_date
FROM hr.employees
WHERE department_id = 20;
COMMIT; --INSERT문에서 TRANSACTION 발생되었음
ora10에 있는 emp테이블에다가 hr.employees 속에 있는 department_id = 20인 사원을 추가시켰다. emp_copy 라는 테이블을 만들어서 employees 테이블의 구조를 복사시키고 데이터를 넣어보자.
...
CREATE TABLE emp_copy
AS SELECT *
FROM hr.employees
WHERE 1 = 2;
INSERT INTO emp_copy
SELECT *
FROM hr.employees;
emp_copy1이라는 테이블을 만들어 departments 테이블을 복사시켜보자.
...
CREATE TABLE emp_copy1
AS SELECT *
FROM hr.departments;
제약조건
제약조건은 데이터에 대한 품질을 좋게하는 방법이다. 제약조건은 DML 문장이 수행될 때 실행된다.
Primary key (대표키)
유일한 값과 NULL 값을 체크한다. 즉 유일한 값이 들어오고 NULL이 들어오지 않는 것을 확인하는 제약조건
>Syntax
ALTER TABLE 테이블명
ADD CONSTRAINTS 제약조건이름
PRIMARY KEY (컬럼명);
/*
CREATE TABLE emp
AS SELECT employee_id, last_name, department_id
FROM hr.employees
WHERE 1=2;
*/
ALTER TABLE emp ADD CONSTRAINTS emp_id_pk PRIMARY KEY(employee_id);
-- emp_id_pk는 제약조건이름으로 고유한 이름으로 설정해줘야 한다.
SELECT *
FROM user_constraints
WHERE table_name = 'EMP'; --EMP테이블의 제약조건 확인
employee_id에 RPIMARY KEY의 제약조건을 설정했다. 이후 employee_id에는 동일한 값과 NULL값이 들어오지 못한다.
Foreign key (외래키)
Primary key와 Unique의 값만 참조 할 수 있는 제약조건. Foreign Key는 중복과 Null을 허용한다.
>Syntax
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건이름
FOREIGN KEY (컬럼명)
REFERENCES 참조테이블(컬럼명)
...
/*
CREATE TABLE dept
AS SELECT * FROM hr.departments;
ALTER TABLE dept
ADD CONSTRAINT dept_id_pk PRIMARY KEY(department_id);
SELECT *
FROM user_constraints
WHERE table_name = 'DEPT';
*/
ALTER TABLE emp
ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY (department_id)
REFERENCES dept(department_id);
dept테이블의 department_id 컬럼을 emp의 department_id컬럼에 Foreign key를 걸었다.
CHECK
조건식이 TRUE인 값을 받는다. (NULL을 허용한다)
CREATE TABLE emp
(id number,
name varchar2(20),
sal number,
dept_id number,
CONSTRAINT emp_sal_ck check(sal > 500));
UNIQUE
중복된 값만 확인을하고 null값은 허용하는 제약조건
CREATE TABLE dept
(dept_id number CONSTRAINT dept_id_pk PRIMARY KEY,
dept_name varchar2(20) CONSTRAINT dept_name_uk UNIQUE);
NOT NULL 제약조건
MODIFY을 사용하여 조건절을 만든다. 테이블 레벨 정의는 사용할 수 없고 열 레벨 정의에서만 사용 가능하다.
ALTER TABLE emp MODIFY name
CONSTRAINT emp_name_nn NOT NULL;
NULL을 허용하는 것으로 수정하고 싶다면?
ALTER TABLE emp MODIFY name NULL;
--혹은
ALTER TABLE emp DROP CONSTRAINT emp_name_nn;
테이블을 만들면서 NOT NULL 제약 조건을 걸고 싶다면, 열 레벨 정의에서만 가능하다.
제약조건 삭제
ALTER TABLE 테이블명 DROP PRIMARY KEY;
...
ALTER TABLE emp DROP PRIMARY KEY;
ALTER TABLE dept DROP PRIMARY KEY CASCADE;
--dept는 emp와 PK-FK 관계를 맺고 있어서 두 키 모두 삭제 할때 CASCADE 사용
ALTER TABLE dept DROP CONSTRAINT dept_id_pk CASCADE;
--위와 같은 쿼리
열/테이블 레벨 정의
열 레벨 정의와 테이블 레벨 정의를 이용해 테이블 생성과 동시에 제약조건을 걸 수 있다.
- 열 레벨 정의: 해당 컬럼에 제약조건 기술
- 테이블 레벨 정의: 컬럼을 다 기술 한 다음 마지막에 ,로 연결하여 제약조건 기술
열 레벨 정의
CREATE TABLE emp
(id number CONSTRAINT emp_id_pk PRIMARY KEY,
name varchar2(20),
sal number CONSTRAINT emp_sal_ck check(sal > 500),
dept_id number);
열 레벨 정의에서 주의해야 할 점
-
NOT NULL 제약조건은 열레벨정의에서만 가능
-
열레벨에서 Foreign key 제약조건을 쓸 시에는 foreign key를 언급하면 안된다.
CREATE TABLE emp
(id number,
name varchar2(20) CONSTRAINT emp_name_nn NOT NULL,
sal number,
dept_id number CONSTRAINT emp_dept_id_fk REFERENCES dept(dept_id));
테이블 레벨 정의
CREATE TABLE emp
(id number ,
name varchar2(20),
sal number,
dept_id number,
CONSTRAINT emp_id_pk PRIMARY KEY(id),
CONSTRAINT emp_sal_ck check(sal > 500));
SELECT * FROM user_cons_columns; --컬럼에 걸려져있는 제약조건 확인
제약조건 활성화/비활성화
ENABLE VALIDATE(기본값): 제약조건을 활성화(enable)시키면서 기존데이터를 검증(validate)ENABLE NOVALIDATE: 제약조건을 활성화(enable)시키면서 기존데이터를 검증하지 않음(novalidate)DISABLE NOVALIDATE: 제약조건을 만들어 놓되, 비활성화시켜서 수행하지는 않음 (PK와 UNIQUE는ENABLE이 불가)DISABLE VALIDATE: 제약조건을 만들어 놓되, DML문 작업을 불허함
CREATE TABLE test(id number not null DISABLE NOVALIDATE);
SELECT * FROM user_constraints where table_name = 'TEST';
ALTER TABLE test MODIFY id constraint test_id_nn not null DISABLE VALIDATE;
INSERT INTO test(id) values(null);
PK 조건에 위반되는 데이터 검색
>Syntax
ALTER TABLE 테이블명 ENABLE CONSTRAINT 제약조건이름
EXCEPTIONS INTO 테이블(exceptions)
테이블을 만들어 PK값을 위반하는 데이터를 찾으려고 한다.
CREATE TABLE test(id number, name char(10), sal number);
INSERT INTO test(id, name, sal)
VALUES(1,'a',1000);
INSERT INTO test(id,name,sal)
VALUES(2,'b',100);
INSERT INTO test(id,name,sal)
VALUES(1,'a',2000);
COMMIT;
ALTER TABLE test ADD CONSTRAINT test_id_pk PRIMARY KEY(id) DISABLE;
--DISABLE을 걸어 제약조건만 걸어두었다.
SELECT * FROM user_constraints where table_name = 'TEST';
PK 제약조건이 걸리지 않아 우선 DISABLE 조건을 걸어 제약조건만 등록시켰다. 그 다음 제약조건에 위반되는 데이터를 찾는 테이블을 생성한다. 이 테이블은 SQL Developer에서는 안되고 SQL +에서 수행해야 한다.
SQL>@%ORACLE_HOME%\rdbms\admin\utlexpt1 --Windows platform
SQL>@$ORACLE_HOME/rdbms/admin/utlexpt1 --unix,linux platform
Windows에서 %ORACLE_HOME% 은 논리적인 경로를 말한다.(상세주소: C:\oraclexe\app\oracle\product\11.2.0\server)
...
select * from tab;
desc exceptions;
테이블을 검색해보니 exceptions 테이블이 만들어졌다.
...
ALTER TABLE test ENABLE CONSTRAINT test_id_pk
EXCEPTIONS INTO exceptions;
pk값에 위반되는 데이터를 exceptions 테이블에 넣었다.
...
SELECT * FROM exceptions;
SELECT * FROM test WHERE rowid = 'AAAE+RAAEAAAAH8AAC';
SELECT * FROM test WHERE rowid = 'AAAE+RAAEAAAAH8AAA';
rowid 값을 찾아 어떤 데이터인지 찾아보고 이 데이터를 수정해보자.
...
UPDATE test
SET id = 3
WHERE ROWID = 'AAAE+dAAEAAAAH8AAA';
COMMIT;
데이터를 수정했다. 이제 pk 제약조건을 활성화시킨다.
...
ALTER TABLE test ENABLE CONSTRAINT test_id_pk;
select * from user_constraints where table_name = 'TEST';
disable 된 조건값을 enable로 수정하려면 테이블명 뒤에 사용해야한다. 처음 등록할때는 뒤에 쓰는데 수정할 때 앞에 써야하는 것을 유의하자.
그럼 위 예시에서 sal이 1500 이상인 조건을 걸어보고 위반되는 데이터를 NULL로 수정해보자
ALTER TABLE test ADD CONSTRAINT test_sal_ck CHECK(sal>1500) ENABLE NOVALIDATE;
DELETE FROM exceptions;
ALTER TABLE test ENABLE CONSTRAINT test_sal_ck
EXCEPTIONS INTO exceptions;
SELECT * FROM exceptions;
SELECT * FROM test WHERE rowid = 'AAAE+jAAEAAAAH8AAA';
SELECT * FROM test WHERE rowid = 'AAAE+jAAEAAAAH8AAB';
UPDATE test
SET sal = NULL
WHERE rowid IN ('AAAE+jAAEAAAAH8AAA','AAAE+jAAEAAAAH8AAB');
ALTER TABLE test ENABLE CONSTRAINT test_sal_ck;
잘 수행된다.
다중테이블 INSERT
INSERT ALL
우선 INSERT 구문을 정리해보자
INSERT INTO table명(컬럼명)
VALUES(..)
INSERT INTO table명(컬럼명)
SELECT * FROM ..
이제 2개의 테이블을 만들어 데이터를 채워보자.
CREATE TABLE sal_history
AS SELECT employee_id, hire_date, salary
FROM employees
WHERE 1=2;
CREATE TABLE mgr_history
AS SELECT employee_id, manager_id, salary
FROM employees
WHERE 1=2;
INSERT ALL
INTO sal_history(employee_id, hire_date, salary)
VALUES(id, hire, sal)
INTO mgr_history(employee_id, manager_id, salary)
VALUES(id, mgr, sal)
SELECT employee_id id, hire_date hire, manager_id mgr, salary sal --SOURCE TABLE
FROM employees;
SELECT * FROM sal_history;
SELECT * FROM mgr_history;
VALUES절에는 소스 테이블의 컬럼명 혹은 별칭을 사용해야 한다. 또한 아직 TRANSACITON이 진행중임을 잊지 말고 COMMIT 혹은 ROLLBACK을 실행하자.
조건 INSERT ALL
예문을 보며 살펴보자.
INSERT ALL
WHEN hire < to_date('2005-01-01','yyyy-mm-dd') THEN
INTO emp_history(employee_id, hire_date,salary)
VALUES(id,hire,sal)
WHEN comm IS NOT NULL THEN
INTO emp_sal(employee_id,commission_pct,salary)
VALUES(id,comm,sal)
SELECT employee_id id, hire_date hire, salary sal, commission_pct comm
FROM employees;
SELECT * FROM emp_history;
SELECT * FROM emp_sal;
CASE와 DECODE와 다르게 INSERT ALL의 WHEN절은 모든 WHEN절의 조건을 수행하여 값을 찾는다. 소스 테이블의 행이 107개니 위의 절은 214번의 WHEN절을 시행하게 된다.
조건 FIRST INSERT
조건에 해당되는 것을 차례대로 INSERT 하는 방법이다.
INSERT FIRST
WHEN salary < 5000 THEN
INTO sal_low(employee_id, last_name, salary)
VALUES(employee_id, last_name, salary)
WHEN salary BETWEEN 5000 AND 10000 THEN
INTO sal_mid(employee_id, last_name, salary)
VALUES(employee_id, last_name, salary)
ELSE
INTO sal_high(employee_id, last_name, salary)
VALUES(employee_id, last_name, salary)
SELECT employee_id, last_name, salary
FROM employees;
MERGE (OLTP와 DW 데이터 이관)
OLTP 환경에서 DW로 이행 작업(INSERT/UPDATE/DELETE) 시 사용된다. DML문은 아래와 같이 이행작업이 수행된다.
- OTLP와 DW의 KEY 값 일치 할 경우:
UPDATE - KEY값 불일치(데이터가 없을 경우):
INSERT - KEY값이 일치하고, flag값이
d일 경우:DELETE
>Syntax
MERGE INTO 타겟테이블
USING 소스테이블
ON 조인 조건
WHEN MATCHED THEN
UPDATE SET
업데이트 조건
DELETE WHERE
딜리트 조건
WHEN NOT MATCHED THEN
INSERT(타겟테이블 컬럼)
VALUES(소스테이블 컬럼);
OLTP와 DW 환경을 간단하게 만들어 MERGE를 수행해보자
--OLTP와 DW 환경의 테이블 생성
CREATE TABLE oltp_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM employees;
CREATE TABLE dw_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = 20;
--FLAG 컬럼 생성
ALTER TABLE oltp_emp
ADD flag CHAR(1);
--OLTP환경에서 데이터가 업데이트됨을 가정
UPDATE oltp_emp
SET flag = 'd'
WHERE employee_id = 202;
UPDATE oltp_emp
SET salary = 20000
WHERE employee_id = 201;
COMMIT;
--MERGE
MERGE INTO dw_emp d --TARGET TABLE
USING oltp_emp o --SOURCE TABLE
ON (d.employee_id = o.employee_id) --ON절은 JOIN조건
WHEN MATCHED THEN --키값이 일치가 되면
UPDATE
SET --UPDATE 시 조건
d.last_name = o.last_name,
d.salary = o.salary * 1.1,
d.department_id = o.department_id
DELETE --DELETE 조건
WHERE o.flag = 'd' --DELETE는 WHERE절이 가능함 (UPDATE 불가)
WHEN NOT MATCHED THEN --키값이 일치되지 않으면
INSERT(d.employee_id,d.last_name,d.salary,d.department_id) --INSERT INTO가 아님을 주의
VALUES(o.employee_id,o.last_name,o.salary,o.department_id);
--MERGE 확인
SELECT * FROM dw_emp WHERE employee_id IN (201,202);
SELECT * FROM oltp_emp WHERE employee_id IN (201,202);
COMMIT;
DATA EXPORT와 EXTERNAL TABLE
DATA EXPORT
ORACLE DEVELOPER에서 가능하다. 도구-데이터베이서 익스포트에서 데이터를 추출할 수 있다. DDL문을 제외, CSV FILE, MS949(ENCODING) 사용해서 테이블(토글에서 테이블만 선택)을 추출했다. (날짜 데이터의 경우 도구-환경설정-데이터베이스-NLS에서 날짜 형식을 지정해주자)
SQL Developer 가 없을 시
SQL Developer 가 없는 사이트에서는 스크립트를 만들어서 DB의 데이터를 TEXT FILE로 떨어트린다. ORACLE DB에 접속할 수 있는 사이트면 권한을 부여받지 않아도 사용할 수 있다.
우선 TEXT FILE 에 다음과 같은 스크립트를 작성한다.
SET PAGESIZE 0
SET LINESIZE 200
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
SPOOL C:\DATA\emp_sal.dat
SELECT employee_id || ',' || last_name || ',' || hire_date || ',' || salary || ',' || department_id
FROM employees
ORDER BY 1;
SPOOL OFF
이 파일을 디렉토리에 sql확장자로 저장을 한 다음 TERMINAL(SQL +)에서 DB에 접속 후 디렉토리 문장을 실행시킨다.
SQL> @c:\data\emp.sql
위 디렉토리에 emp_sal.dat로 저장되어 있다. 이를 아래 방식대로 EXTERNAL TABLE을 만들 수 있다.
EXTERNAL TABLE
HR에서 디렉토리와 권한을 USER에게 부여하고, USER가 DATA를 사용할 수 있게 만든다.
- DBA로 접속
오라클에서 물리적인 디렉토리 대신 논리적인 디렉토리를 설정해주어야 한다.
CREATE DIRECTORY data_dir AS 'C:\data\'; --data_dir은 디렉토리 이름
--DROP DIRECTORY data_dir --디렉토리 삭제
SELECT * FROM dba_directories; --디렉토리 생성 확인
--디렉토리 생성 후 오브젝트 권한을 부여를 해주어야함
GRANT READ, WRITE ON DIRECTORY data_dir TO hr; --WRITE는 LOG정보를 저장
--REVOKE READ, WRITE ON DIRECTORY data_dir FROM hr; --권한 회수
- USER 입장
이제 TABLE을 CREATE 할 수 있는 권한이 있으면 EXTERNAL TABLE을 만들수 있다.
SELECT * FROM user_tab_privs WHERE table_name = 'DATA_DIR'; --권한 부여 확인
SELECT * FROM all_directories WHERE directory_name = 'DATA_DIR'; --디렉토리 확인
CREATE TABLE empxt
(id number,
name varchar2(30),
hire_date date,
sal number,
dept_id number)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader --ORACLE에서 파일을 읽을 수 있는 TYPE(ORACLE_LOADER 프로그램을 사용)
DEFAULT DIRECTORY data_dir --논리적인 디렉토리 경로를 사용해야
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE --행(레코드)를 한줄씩 가져와서 사용
BADFILE 'empxt.bad' -- 저장이 안되는 레코드를 BAD파일에 저장
LOGFILE 'empxt.log'
FIELDS TERMINATED BY ',' --CSV파일이 COMMA로 연결되어 있어 ',' 사용
MISSING FIELD VALUES ARE NULL --빈 값을 NULL로 처리
(id,name,hire_date CHAR DATE_FORMAT DATE MASK "yyyy-mm-dd", sal, dept_id)
--FILE속 날짜데이터는 CHAR형(yyyy-mm-dd)이지만 hire_date는 date형으로 만들어서 형일치를 시켜줘야함
)
LOCATION('emp1.csv')
--LOCATION('emp1.csv','emp_30.csv'). 같은 형식의 데이터 파일이면 연결지어서도 가능함
)
REJECT LIMIT UNLIMITED; --key word로 생각하고 외우자 (문제되는 파일을 reject)
SELECT * FROM empxt;
SELECT * FROM user_external_tables; --external table 확인
SELECT * FROM user_external_locations; --external file 확인
SELECT * FROM all_directories; --directory 확인
SELECT * FROM user_tab_privs; --오브젝트 권한 확인
--혹시 주석때문에 오류 발생할 수 있으니 오류가 발생하면 주석을 지우고 사용하자
OS에 저장되어 있는 파일을 DB에서 사용하기 위해 연결만 시켜놓은 상태이다.(메타 테이블) EXTERNAL TABLE은 READ(SELECT)만 가능하다는 한계가 있다(DML작업/INDEX설계 불가). 사용하려면 DB에 저장을 시켜서 사용하게 해야한다. 그래서 CTAS를 사용하여 EXTERNAL TABLE을 DB에 저장시켜 사용한다.
...
CREATE TABLE empxt_copy
AS
SELECT * FROM empxt;