240104 Oralce DB Linux _ SQL LOADER

[oracle1@oracle ~]$ cd spool/
[oracle1@oracle spool]$ vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE emp_sal.csv
TRUNCATE <- 수정
INTO TABLE hr.emp_sal
FIELDS TERMINATED BY ','
(employee_id, last_name, first_name, salary, department_id)

[oracle1@oracle spool]$ ls
emp_sal.bad (null 값의 row)  emp_sal.ctl  emp_sal.sql  insa.log
emp_sal.csv  emp_sal.log  insa.ctl
[oracle1@oracle spool]$ vi emp_sal.ctl
[oracle1@oracle spool]$ sqlldr hr/hr control=emp_sal.ctl direct=true

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Jan 4 09:47:46 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Load completed - logical record count 107.

[oracle1@oracle spool]$ cat emp_sal.bad
178,Grant,Kimberely,7000,

#) ROW의 수 변동

SELECT COUNT(*) FROM hr.emp_sal;

[oracle1@oracle spool]$ vi emp_sal.log
[oracle1@oracle spool]$ vi emp_sal.ctl

UNRECOVERABLE LOAD DATA
INFILE emp_sal.csv
TRUNCATE
INTO TABLE hr.emp_sal
FIELDS TERMINATED BY ','
TRAILING NULLCOLS <- 수정, null 값이 포함시키기 위함이다.

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

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Jan 4 09:57:12 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Load completed - logical record count 107.

SELECT COUNT(*) FROM hr.emp_sal;

#) 연습해보기
#1) control file, data file 생성
[oracle1@oracle spool]$ vi insa.ctl

LOAD DATA -- 기본값
INFILE insa.dat
INSERT
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)

[oracle1@oracle spool]$ vi insa.dat

1,"JAMES","010-1000-0001"
2,"ORACLE","010-7777-7777"
3,"LEE","010-3333-3333"
3,"SCOTT","010-9999-9999"

#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) 로드하기 : Conventional load
- 장점 : ⓐ 성능(품질) 좋은 데이터만 입력된다. ⓑ 제약조건 체크해서 문제되는 데이터를 bad file에 입력해준다. ⓒ 소량 data 에서 추천
- 단점 : ⓐ 수행 속도 느림. ⓑ 리두 발생을 한다. ⓒ commit 발생한다. ⓓ 대량 data 에서는 좋지 않음.
[oracle1@oracle spool]$ sqlldr hr/hr control=insa.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Jan 4 10:08:49 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 4

#4) data 확인

SELECT * FROM hr.test;

# 대량의 데이터를 load 작업시
1. 테이블의 제약조건은 disable 상태로 변경한다. 
2. sqlldr 를 통해서 direct path load 방식으로 로드 한다. 
3. 테이블의 제약조건은 enable 상태로 변경한다. 단, 제약조건인 enable 상태로 변경시에 제약조건에 위반되는 데이터를 찾아서 변경해야 한다.

-- 연습
#1) control file 생성
[oracle1@oracle spool]$ vi insa.ctl

UNRECOVERABLE LOAD DATA
INFILE insa.dat
TRUNCATE
INTO TABLE hr.test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID, NAME, PHONE)

tip 면접) delete(row들만 삭제, block존재) vs truncate(전부 삭제, no복구)

# direct path load
- 장점 : ⓐ 수행속도가 빠르다. ⓑ 테이블에 저장해야할 데이터를 디스크로 바로 save 한다.(단, 메모리에서 모양을 만든 후에 바로 붙인다.)
- 단점 : ⓐ 제약조건을 체크하지 않는다. ⓑ commit 발생하지 않는다.

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

#3) data 확인

SELECT * FROM hr.test;

#4) 제약조건 확인

SELECT owner, constraint_name, constraint_type, table_name, status, deferrable, deferred, validated 
FROM user_constraints 
WHERE table_name = 'TEST';

SELECT index_name, index_type, table_owner, table_name, status 
FROM user_indexes 
WHERE table_name = 'TEST';

SELECT * FROM user_ind_columns WHERE table_name = 'TEST';
SELECT * FROM user_objects WHERE status = 'INVALID';

#4-1) full scan 으로 확인

SELECT * FROM hr.test WHERE id = 1;

<SYS SESSION>
# index 상태가 UNUSABLE 상태일 경우 : DML 문 실행 불가

SELECT * FROM dba_indexes WHERE owner = 'HR' and  table_name = 'TEST';

SELECT status FROM dba_indexes WHERE owner = 'HR' and  table_name = 'TEST';

 

    -- UNUSABLE 상태(실행 불가능한 상태): index가 깨진 상태

 

- 기존의 index scan 이 table full scan 으로 바뀐다.

SELECT * FROM hr.test WHERE id = 1;

1) INSERT 수행 불가

INSERT INTO hr.test(id, name, phone) VALUES (4, 'PARK', '010-0001-1005');

 

/*오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다*/

#) DATA 확인

- 변화 없음

SELECT * FROM hr.test;

2) DELETE 수행 불가

DELETE FROM hr.test WHERE name = 'SCOTT';

/*오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다*/

3) UPDATE 수행 불가

UPDATE hr.test 
SET id = 4
WHERE name = 'SCOTT';

/*오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다*/

# 해결방법 순서
#1) 제약조건 disable 변경 

ALTER TABLE hr.test DISABLE CONSTRAINT test_id_pk;

#2) 변경 확인 

SELECT * FROM dba_constraints WHERE owner = 'HR' and table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' and  table_name = 'TEST';

#3) 로컬창에서 
SQL> @$ORACLE_HOME/rdbms/admin/utlexpt1
Table created.
#4) 확인

SELECT * FROM exceptions;

#5) 변경 
- hr 소유자의 exceptions 테이블에 로드한다.

ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id_pk EXCEPTIONS INTO hr.exceptions;

/*오류 보고 -
ORA-02437: (HR.TEST_ID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"*/
#6) exceptions 확인

TRUNCATE TABLE hr.exceptions;
SELECT * FROM hr.exceptions;

#tip) sys exceptions 테이블에 로드

TRUNCATE TABLE sys.exceptions;
ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id_pk EXCEPTIONS INTO sys.exceptions;
SELECT * FROM sys.exceptions;

#7) row_id 로 찾기

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

#8) data 변경 및 저장

UPDATE hr.test
SET id = 4
WHERE ROWID = 'AAAVqeAAEAAAAJrAAD';
COMMIT;

#9) data 확인

SELECT * FROM hr.test;

#10) 지우기

TRUNCATE TABLE hr.exceptions;

#11) 상태 변경

ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id_pk EXCEPTIONS INTO hr.exceptions;

#12) 상태 변경 확인 

SELECT * FROM dba_constraints WHERE owner = 'HR' and table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' and  table_name = 'TEST';
SELECT * FROM dba_ind_columns WHERE index_owner = 'HR' and table_name = 'TEST';

#) index scan 확인 

SELECT * FROM hr.test WHERE id = 1;

#) 시퀸스 적용하기  

#1) sql 생성
[oracle1@oracle spool]$ vi emp_new.sql

set pagesize 0
set linesize 200
set echo off
set termout off
set trimspool on 
set feedback off
spool emp_new.csv
select upper(last_name)||','||salary||','||to_char(hire_date, 'yyyy-mm-dd')
from hr.employees;
spool off

 

#2) 터미널 창에서 .sql 실행시켜주기
[oracle1@oracle spool]$ sqlplus hr/hr
SQL> @emp_new.sql

 

#3) 기존 테이블 삭제

DROP TABLE hr.emp_new PURGE;

#4) 테이블 생성

CREATE TABLE hr.emp_new(
    employee_id number, 
    last_name varchar2(20),
    salary number, 
    hire_date date );

#5-1) employee_id 시퀸스 번호로 지정    
[oracle1@oracle spool]$ vi emp_new.ctl

UNRECOVERABLE LOAD DATA
INFILE emp_new.csv
INSERT 
INTO TABLE hr.emp_new
FIELDS TERMINATED BY ','
(employee_id sequence(max,1), last_name, salary, hire_date date 'yyyy-mm-dd')
                      시퀀스 번호(끝번호,시작번호)

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

#6) 적용되었는지 테이블 확인

SELECT * FROM hr.emp_new;

#5-2) employee_id 시퀸스 번호 변경
[oracle1@oracle spool]$ vi emp_new.ctl

UNRECOVERABLE LOAD DATA
INFILE emp_new.csv
TRUNCATE
INTO TABLE hr.emp_new
FIELDS TERMINATED BY ','
(employee_id sequence(100,1), last_name, salary, hire_date date 'yyyy-mm-dd')
                      시퀀스 번호(시작번호, 증가분)

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

#6) 적용되었는지 테이블 확인

SELECT * FROM hr.emp_new;