240103 Oracle DB Linux_데이터 제약 조건 상태, 데이터 이관 작업

■ 제약 조건 상태 

제약 조건 상태	해석
disable novalidate	제약조건을 생성은 하지만 제약조건에 대한 체크는 하지 않겠다. 즉, 새롭게 들어오는 데이터든 기존 데이터든 제약조건은 검증하지 않겠다.
disable validate	제약조건을 생성하지만 새로운 데이터에 대해서 DML 은 불허하다.  트랜잭션 작업 막을 때
enable validate	제약조건을 생성하면서 새로운 데이터와 기존 데이터 둘다 검증한다.  활성화+모든data검증, 실무적으로 많이 운영
enable novalidate	제약조건을 생성하면서 새로운 데이터만 검증한다. 기존데이터에 대해서는 검증하지 않는다.  활성화+새로운data만 검증 PK, UQ 는 제외

1. disable 기본 값은 disable novalidate 값이다. 비활성화+새로운 데이터만 검증, ex) 데이터이관작업_제약조건
2. enable 기본 값은 enable validate 값이다.

□ primary key, unique 제약 조건은 enable novalidate 성격을 수행하더라도 기존 데이터에 대해서 검증 체크한다. 

(중복, null 체크)
- 위반 시 오류발생 unique index 를 생성하기 때문에 

ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY ( id ) enable validate;
ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY ( id ) enable novalidate; -- 검증을 하지 않는다해도 검증함.

ALTER TABLE hr.test ADD CONSTRAINT test_id_uk UNIQUE ( id ) enable validate;
ALTER TABLE hr.test ADD CONSTRAINT test_id_uk UNIQUE ( id ) enable novalidate;

# 제약조건 확인

SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_indexes WHERE table_name = 'TEST';

SQL> select employee_id||','||last_name||','||first_name||','||salary||','||department_id 
from hr.employees;

SQL> set pagesize 1000
SQL> /

SQL> spool emp_sal.dat
SQL> select employee_id||','||last_name||','||first_name||','||salary||','||department_id 
from hr.employees;

SQL> spool off
SQL> !
[oracle1@oracle ~]$ ls
database   Downloads    p13390677_112040_LINUX_1of7.zip  Public     Videos
Desktop    emp_sal.dat  p13390677_112040_LINUX_2of7.zip  Templates
Documents  Music        Pictures                         userdata
[oracle1@oracle ~]$ vi emp_sal.dat

 

 

#) data만 출력 후 갖고 있기
[oracle1@oracle ~]$ rm emp_sal.dat
[oracle1@oracle ~]$ vi emp_sal.sql

# data만 출력해야 할 때

- 미리 메모장에 작성해놓을 것!

vi emp_sal.sql

set pagesize 0
set linesize 200
set echo off
set termout off
set trimspool on 
set feedback off
spool emp_sal.csv
select employee_id||','||last_name||','||first_name||','||salary||','||department_id 
from hr.employees;
spool off

-- 해석
set pagesize 0      : 한 페이지로 지정할 라인 수, 0으로 설정하면 컬럼은 출력하지 않는다.
set linesize 200    : 한 라인에 표시할 글자의 수
set echo off        : 실행하는 SQL문을 출력하지 않는다.
set termout off     : SQL문으로 실행한 결과를 표시하지 않는다.
set trimspool on    : 라인 뒤 공백을 제거
set feedback off    : SQL문으로 실행한 결과 건수를 표시하지 않는다.
spool emp_sal.csv   : SQL문의 실행결과를 spool file로 생성한다.
쿼리문               : 실행할 쿼리문
spool off           : SQL문의 실행결과를 spool file로 생성을 멈추겠다.

 

#) 복제용 딕셔너리 만들기
[oracle1@oracle ~]$ mkdir spool
[oracle1@oracle ~]$ cd spool/
[oracle1@oracle spool]$ pwd
/home/oracle1/spool
[oracle1@oracle spool]$ cd
[oracle1@oracle ~]$ ls

 

#) file 옮기
[oracle1@oracle ~]$ mv emp_sal.sql /home/oracle1/spool
[oracle1@oracle ~]$ cd /home/oracle1/spool
[oracle1@oracle spool]$ ls

[oracle1@oracle spool]$ sqlplus hr/hr -- 여기서 로그인을 한다면 sqlplus 에서는 home 이다.
SQL> ! pwd
/home/oracle1/spool
SQL> ! ls
emp_sal.sql
SQL> @emp_sal.sql
SQL> ! ls
emp_sal.csv  emp_sal.sql
SQL> ! cat emp_sal.csv

 

#1) 테이블 삭제

DROP TABLE emp_sal PURGE;

#2) 테이블틀만 복제

CREATE TABLE emp_sal
AS
SELECT employee_id, last_name, first_name, salary, department_id
FROM hr.employees
WHERE 1 = 2;

#3) 테이블 확인

DESC emp_sal;

SELECT * FROM hr.emp_sal;

 

 

 

■ SQL LOADER  
1. 데이터 이관 작업하는 프로그램
2. 데이터파일, 데이터 이관될 table 존재시 가능

3. 외부 파일에서 오라클 데이터베이스의 테이블로 data를 load 하는 프로그램

# emp_sal.csv -> emp_sal 테이블에 데이터 이관작업
# control file
vi emp_sal.ctl

 -- 로그정보, 리두 정보 줄이기
UNRECOVERABLE LOAD DATA 
INFILE emp_sal.csv
INSERT 
INTO TABLE hr.emp_sal
FIELDS TERMINATED BY ','
(employee_id, last_name, first_name, salary, department_id)

[oracle1@oracle spool]$ vi emp_sal.ctl

[oracle1@oracle spool]$ cat emp_sal.ctl

#) sqlldr : 대량의 insert문으로 대신해서 실행
[oracle1@oracle spool]$ sqlldr hr/hr control=emp_sal.ctl direct=true

Load completed - logical record count 107. -- 로직완료

#) data 이관 작업 확인

SELECT * FROM hr.emp_sal;

# 연습용, 테이블 내에 data가 없을 경우 insert문 가능
#1) insa 추가
vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE *
INSERT
INTO TABLE hr.test
FIELDS TERMINATED BY ','
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-7777-7777"
3,"LEE","010-3333-3333"

#2) 테이블 삭제 및 생성

DROP TABLE hr.test PURGE;

CREATE TABLE hr.test (
    ID NUMBER CONSTRAINT test_id_pk PRIMARY KEY,
    NAME VARCHAR2(30) ,
    phone VARCHAR2(20)
);

#3) 로드하기
[oracle1@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true

#4) data 이관작업 확인

SELECT * FROM hr.TEST; -- "" 삽입
TRUNCATE TABLE hr.TEST; -- 내용 삭제
SELECT * FROM hr.TEST; -- data 확인

#5) 수정, OPTIONALLY ENCLOSED BY '"' 추가
[oracle1@oracle spool]$ vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE *
INSERT
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-7777-7777"
3,"LEE","010-3333-3333"

[oracle1@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true

#6) data 확인

SELECT * FROM hr.test;

#) replace 로 변경 후 확인
vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE *
TRUNCATE  -- (REPLACE 보단 리소스에서 더 낫다)
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-7777-7777"
3,"LEE","010-3333-3333"

 

[oracle1@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true

 

#) data 확인

SELECT * FROM hr.test;

# INSERT : 비어있는 테이블에 입력할 때 사용 
# REPLACE : 기존 행을 DELETE한 후 데이터를 입력한다.
# truncate : 테이블을 truncate 한 후 새로운 데이터를 입력한다.
# APPEND : 새로운 데이터를 기존 테이블의 추가 한다.

SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_cons_columns WHERE table_name = 'TEST';

UNRECOVERABLE LOAD DATA
INFILE *
APPEND --id가 PK로 되어 있어도 중복으로 로드
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)
BEGINDATA
1,"JAMES","010-1000-0001"
2,"ORACLE","010-7777-7777"
3,"LEE","010-3333-3333"

[oracle1@oracle spool]$ sqlldr hr/hr control=insa.ctl direct=true

 

#) data 확인

SELECT * FROM hr.test;
SELECT * FROM hr.test WHERE id = 1;

#) 제약조건 상태 확

SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_cons_columns WHERE table_name = 'TEST';
SELECT * FROM user_indexes WHERE table_name = 'TEST';

SELECT * FROM  user_objects WHERE object_name = 'TEST_ID_PK';

#) 제약조건 변경

alter table hr.test disable constraint test_id_pk;
SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_indexes WHERE table_name = 'TEST'; -- 자동으로 index 제거

#) 제약조건 enable 변경

alter table hr.test enable constraint test_id_pk; -- 오류발생

#) 지우고 제약조건 변경

truncate table exceptions;
alter table test enable constraint test_id_pk exceptions into exceptions;

#) 중복된 DATA 확인

SELECT rowid, t.*
FROM test t
WHERE rowid in (select row_id from exceptions);

#) 중복된 DATA 삭제 후 저장. 

delete from test 
where rowid in (
'AAAVppAAEAAAAJMAAA',
'AAAVppAAEAAAAJMAAB',
'AAAVppAAEAAAAJMAAC' );

commit;
select * from test;

#) 기존 DATA 지우고 제약조건 변경

truncate table exceptions;
alter table test enable constraint test_id_pk exceptions into exceptions;

#) 제약조건, index 확인

SELECT * FROM user_constraints WHERE table_name = 'TEST';
SELECT * FROM user_indexes WHERE table_name = 'TEST';