[아이티윌 오라클 DBA 과정 91기] 260115 TIL

제약 조건 상태

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key,
    sal number constraint sal_ck check(sal > 1000))
tablespace users;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';
select * from dba_indexes where owner = 'HR' and table_name = 'TEST';

• 제약 조건은 활성화(enable)되거나 비활성화(disable) 될 수 있음
• 제약조건이 활성화되면 데이터베이스에 데이터가 입력/갱신/삭제 될 때 검사 수행
• 제약 조건 규칙을 따르지 않으면 데이터는 입력/갱신/삭제 할 수 없음
• 제약 조건이 비활성화되면 규칙을 따르지 않는 데이터를 입력/갱신/삭제할 수 있음

제약 조건 상태

enable validate

• enable의 기본 값은 validate
• 기존 데이터와 새로 들어오는 데이터 모두에 대해 제약 조건 검증
• 기존 데이터 체크하기 위해 테이블에 lock이 걸림
• 제약 조건(pk, unique)에 의해 유니크 인덱스가 자동으로 생성됨

insert into hr.test(id, sal) values(1, 2000);
select * from hr.test;

insert into hr.test(id, sal) values(1, 2001);

명령의 12 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

명령의 14 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(2, 1000)
오류 보고 -
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다

rollback;

disable novalidate

• disable의 기본 값은 novalidate
• 제약 조건 검증 안 함(기존 데이터, 새로운 데이터 모두)
• 제약 조건(pk, unique)에 의해 자동으로 생성된 유니크 인덱스는 삭제됨

alter table hr.test disable constraint test_id_pk;
alter table hr.test disable novalidate constraint sal_ck;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

-- pk로 생성된 유니크 인덱스 사라짐
select * from dba_indexes where owner = 'HR' and table_name = 'TEST';

-- 모두 입력됨
insert into hr.test(id, sal) values(1, 2000);
insert into hr.test(id, sal) values(1, 2001);
insert into hr.test(id, sal) values(2, 1000);

commit;

enable novalidate

• 새로운 데이터부터 제약조건을 체크하고 기존 데이터는 제약조건을 체크하지 않음
• 단, pk, unique 제약조건은 수행 불가
• 기존의 데이터가 제약 조건을 만족하지 않는 경우 enable validate 상태로 변경 불가

alter table hr.test enable constraint test_id_pk;
alter table hr.test enable constraint sal_ck;

명령의 55 행에서 시작하는 중 오류 발생 -
alter table hr.test enable constraint test_id_pk
오류 보고 -
ORA-02437: (HR.TEST_ID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate values or null
           values.
*Action:   remove the duplicates and null values before enabling a primary
           key.

명령의 56 행에서 시작하는 중 오류 발생 -
alter table hr.test enable constraint sal_ck
오류 보고 -
ORA-02293: (HR.SAL_CK)을 검증할 수 없습니다 - 잘못된 제약을 확인합니다
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

• pk와 unique 제약 조건은 유니크 인덱스를 생성해야 되는데 중복이 있으면 만들 수가 없음 → 무조건 enable validate만 가능

alter table hr.test enable novalidate constraint test_id_pk;

명령의 55 행에서 시작하는 중 오류 발생 -
alter table hr.test enable novalidate constraint test_id_pk
오류 보고 -
ORA-02437: (HR.TEST_ID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate values or null
           values.
*Action:   remove the duplicates and null values before enabling a primary
           key.

• 다른 제약 조건은 enable novalidate로 변경 가능

alter table hr.test enable novalidate constraint sal_ck;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

• enable novalidate 상태이므로 새로 들어온 데이터에 대해서는 제약 조건 검증함

insert into hr.test(id, sal) values(3, 500);

명령의 70 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(3, 500)
오류 보고 -
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다

disable validate

• 대상 테이블에 dml 작업 수행 불가
• 제약 조건(pk, unique)에 의해 자동으로 생성된 유니크 인덱스는 삭제됨

alter table hr.test disable validate constraint test_id_pk;
alter table hr.test disable validate constraint sal_ck;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

-- 유니크 인덱스 사라짐
select * from dba_indexes where owner = 'HR' and table_name = 'TEST';

insert into hr.test(id, sal) values(1, 2000);

명령의 43 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2000)
오류 발생 명령행: 43 열: 1
오류 보고 -
SQL 오류: ORA-25128: 사용 안함으로 설정되고 검증된 제약 조건(HR.SAL_CK)을 사용하여 테이블에서 삽입/업데이트/삭제 작업이 수행되지 않았습니다.
25128. 00000 -  "No insert/update/delete on table with constraint (%s.%s) disabled and validated"
*Cause:    Try to insert/update/delete on table with DISABLE VALIDATE constraint.
*Action:   Change the constraint's states.

enable validate로 변경 안되는 제약 조건 문제 해결

[oracle@ora19c ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ora19c admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin
[oracle@ora19c admin]$ ls utlexpt*
utlexpt1.sql
[oracle@ora19c admin]$ tail -5 utlexpt1.sql
create table exceptions(row_id urowid,
                        owner varchar2(128),
                        table_name varchar2(128),
                        constraint varchar2(128));

hr 세션에서 exceptions 테이블 생성

create table exceptions(row_id urowid,
                        owner varchar2(128),
                        table_name varchar2(128),
                        constraint varchar2(128));

select * from exceptions;

• 테이블 소유자에게 exceptions 테이블이 없으면 다음 오류 발생

명령의 80 행에서 시작하는 중 오류 발생 -
alter table hr.test enable validate constraint test_id_pk exceptions into exceptions
오류 보고 -
ORA-02445: 예외 테이블이 없습니다
02445. 00000 -  "Exceptions table not found"
*Cause:    the explicity or implicity declared exceptions table does not
           exist.
*Action:   Create the table then issue the enable command again.

문제 되는 데이터 수정

• test_id_pk : id가 동일한 두 데이터로 인한 오류 발생

-- 오류 발생한 데이터를 exceptions 테이블에 입력해줌
alter table hr.test enable validate constraint test_id_pk exceptions into hr.exceptions;
select * from hr.exceptions;

select rowid, id, sal from hr.test where rowid in (select row_id from hr.exceptions);

• 두 번째 데이터의 아이디를 3으로 변경

update hr.test set id = 3 where rowid = 'AAASLCAAHAAAAHEAAB';
select * from hr.test where rowid = 'AAASLCAAHAAAAHEAAB';
commit;

select * from hr.test;

• test_id_pk 제약 조건 상태를 enable validate로 변경

truncate table hr.exceptions;
alter table hr.test enable validate constraint test_id_pk exceptions into hr.exceptions;

-- 오류 없음
select * from hr.exceptions;

# test_id_pk 제약 조건 상태가 enable validate로 변경됨
select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';

# 유니크 인덱스 생성됨
select index_name, uniqueness, status from dba_indexes where owner = 'HR' and table_name = 'TEST';

• sal_ck : sal가 1000보다 작은 데이터로 인해 오류 발생

alter table hr.test enable validate constraint sal_ck exceptions into hr.exceptions;

명령의 98 행에서 시작하는 중 오류 발생 -
alter table hr.test enable validate constraint sal_ck exceptions into hr.exceptions
오류 보고 -
ORA-02293: (HR.SAL_CK)을 검증할 수 없습니다 - 잘못된 제약을 확인합니다
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

select * from hr.exceptions;

select rowid, id, sal from hr.test where rowid in (select row_id from hr.exceptions);

• sal를 1001로 변경

update hr.test set sal = 1001 where rowid = 'AAASLCAAHAAAAHEAAC';
select * from hr.test where rowid = 'AAASLCAAHAAAAHEAAC';
commit;
select * from hr.test;

• sal_ck 제약 조건 상태를 enable validate로 변경

truncate table hr.exceptions;
alter table hr.test enable validate constraint sal_ck exceptions into hr.exceptions;

-- 오류 없음
select * from hr.exceptions;

select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';

제약 조건 나중에 추가하는 경우

drop table hr.test purge;

create table hr.test(id number, sal number)tablespace users;

-- 제약 조건 없음
select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

-- 모든 데이터 입력됨
insert into hr.test(id, sal) values(1, 2000);
insert into hr.test(id, sal) values(1, 2001);
insert into hr.test(id, sal) values(2, 1000);
commit;

제약 조건 추가 시 오류 발생

• 기본적으로 제약 조건을 추가할 때 enable validate 상태로 추가하기 때문에 제약 조건에 부합하지 않는 데이터가 있을 경우 오류 발생함

alter table hr.test add constraints test_id_pk primary key(id);
alter table hr.test add constraints sal_ck check(sal > 1000);

명령의 128 행에서 시작하는 중 오류 발생 -
alter table hr.test add constraints test_id_pk primary key(id)
오류 보고 -
ORA-02437: (HR.TEST_ID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate values or null
           values.
*Action:   remove the duplicates and null values before enabling a primary
           key.

명령의 129 행에서 시작하는 중 오류 발생 -
alter table hr.test add constraints sal_ck check(sal > 1000)
오류 보고 -
ORA-02293: (HR.SAL_CK)을 검증할 수 없습니다 - 잘못된 제약을 확인합니다
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

disable novalidate 상태로 제약조건을 생성

alter table hr.test add constraints test_id_pk primary key(id) disable;
alter table hr.test add constraints sal_ck check(sal > 1000) disable;

• 위에서 enable validate로 변경 안되는 제약 조건 문제를 해결한 방식으로 해결

제약 조건 체크 시점

• 입력/수정/삭제 작업을 수행하는 즉시 제약 조건을 검사할 수 있고(immediate), commit 시에 제약 조건을 검사할 수 있음(deferred)
• set constraints 문을 사용하여 제약 조건을 deferred, immediate 설정하려면 제약 조건을 deferrable로 생성해야 함

not deferrable initially immediate(기본 값)

• not deferrable은 제약 조건 체크 작업을 지연 시킬 수 없음
• DML 수행 시 바로 제약 조건 체크
• pk, unique 제약 조건의 경우 unique index 생성

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key,
    sal number constraint sal_ck check(sal > 1000))
tablespace users;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

insert into hr.test(id, sal) values(1, 2000);

1 행 이(가) 삽입되었습니다.

insert into hr.test(id, sal) values(1, 2001);

명령의 173 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

명령의 174 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(2, 1000)
오류 보고 -
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다

not deferrable initially deffered는 불가

명령의 164 행에서 시작하는 중 오류 발생 -
create table hr.test(
    id number constraint test_id_pk primary key not deferrable initially deferred,
    sal number constraint sal_ck check(sal > 1000) not deferrable initially deferred)
tablespace users
오류 보고 -
ORA-02447: 지연이 가능하지 않은 제약조건을 지연할 수 없습니다
02447. 00000 -  "cannot defer a constraint that is not deferrable"
*Cause:    An attempt was made to defer a nondeferrable constraint
*Action:   Drop the constraint and create a new one that is deferrable

deferrable initially immediate

• 제약 조건 체크를 지연시킬 수 있음
• 기본은 DML 수행 시 바로 제약 조건 체크
• pk, unique 제약 조건의 경우 nonunique index 생성

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key deferrable initially immediate,
    sal number constraint sal_ck check(sal > 1000) deferrable initially immediate)
tablespace users;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

# nonunique 인덱스가 생성됨
select * from dba_indexes where owner = 'HR' and table_name = 'TEST';

insert into hr.test(id, sal) values(1, 2000);

1 행 이(가) 삽입되었습니다.

insert into hr.test(id, sal) values(1, 2001);

명령의 172 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

명령의 173 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(2, 1000)
오류 보고 -
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다

rollback;

현재 세션에서 모든 제약 조건 지연 검사

set constraint all deferred;

# 데이터 모두 입력됨
insert into hr.test(id, sal) values(1, 2000);
insert into hr.test(id, sal) values(1, 2001);
insert into hr.test(id, sal) values(2, 1000);

• commit 시 제약 조건 체크 수행

commit;

명령의 160 행에서 시작하는 중 오류 발생 -
commit
오류 보고 -
ORA-02091: 트랜잭션이 롤백되었습니다
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.

현재 세션에서 특정 제약 조건만 지연 검사

set constraints hr.sal_ck deferred;

insert into hr.test(id, sal) values(1, 2000);

1 행 이(가) 삽입되었습니다.

insert into hr.test(id, sal) values(1, 2001);

명령의 175 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

1 행 이(가) 삽입되었습니다.

commit;

명령의 178 행에서 시작하는 중 오류 발생 -
commit
오류 보고 -
ORA-02091: 트랜잭션이 롤백되었습니다
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.

deferrable initially deferred

• 제약 조건 체크를 즉시 하도록 할 수 있음
• 기본은 commit 수행 시 검사
• pk, unique 제약 조건의 경우 nonunique index 생성

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key deferrable initially deferred,
    sal number constraint sal_ck check(sal > 1000) deferrable initially deferred)
tablespace users;

select * from dba_constraints where owner = 'HR' and table_name = 'TEST';

insert into hr.test(id, sal) values(1, 2000);
insert into hr.test(id, sal) values(1, 2001);
insert into hr.test(id, sal) values(2, 1000);

commit;

명령의 175 행에서 시작하는 중 오류 발생 -
commit
오류 보고 -
ORA-02091: 트랜잭션이 롤백되었습니다
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.

현재 세션에서 모든 제약 조건 즉시 검사

set constraint all immediate;

insert into hr.test(id, sal) values(1, 2000);

1 행 이(가) 삽입되었습니다.

insert into hr.test(id, sal) values(1, 2001);

명령의 172 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

명령의 173 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(2, 1000)
오류 보고 -
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다

rollback;

현재 세션에서 특정 제약 조건 즉시 검사

set constraints hr.test_id_pk immediate;

insert into hr.test(id, sal) values(1, 2000);

1 행 이(가) 삽입되었습니다.

insert into hr.test(id, sal) values(1, 2001);

명령의 175 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, sal) values(1, 2001)
오류 보고 -
ORA-00001: 무결성 제약 조건(HR.TEST_ID_PK)에 위배됩니다

insert into hr.test(id, sal) values(2, 1000);

1 행 이(가) 삽입되었습니다.

commit;

명령의 178 행에서 시작하는 중 오류 발생 -
commit
오류 보고 -
ORA-02091: 트랜잭션이 롤백되었습니다
ORA-02290: 체크 제약조건(HR.SAL_CK)이 위배되었습니다
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.

현재 세션에서 모든 제약 조건에 대해 즉시 / 지연 검사

alter session set constraints = immediate | deffered

텍스트 형식으로 Export

SQL*Loader

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader.html#GUID-628A6D43-DA99-4677-9B88-445928933246

• 외부 파일에서 오라클 데이터베이스의 테이블로 데이터를 로드 작업 수행

컨트롤 파일 내용

• [unrecoverable] load data : 새 데이터를 로드 시작
  • unrecoverable : 아카이브 모드에서 direct path load 사용 시에 리두 로그 정보를 생성하지 않음
• infile insa.dat : 로드 대상 파일 지정, 외부 데이터 파일, 만약에 포함하지 않을 경우 * 표시

load data
infile *
truncate
into table hr.test
fields terminated by ',' optionally enclosed by '"'
(id,name,phone)
begindata
1,"JAMES","010-1000-0000"
2,"GRACE","010-7777-7777"
3,"SCOTT","010-8888-8888"

• insert
  
  • insert : 비어있는 테이블에 데이터 로드
  • truncate : 테이블을 truncate하고 데이터 로드
  • replace : 테이블에 있는 기존 행을 삭제(delete)하고 데이터 로드
  • append : 테이블 기존 행 뒤에 추가
  • insert 옵션은 테이블이 비어있는 경우에만 가능

[oracle@ora19c data]$ sqlldr hr/hr control=insa.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 15 16:13:58 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
SQL*Loader-601: For INSERT option, table must be empty.  Error on table HR.TEST

• into table hr.test : 데이터를 로드할 테이블 지정
• fields terminated by ',' : 필드의 종결 문자 지정
• optionally enclosed by '"' : 입력하지 말아야 할 문자 지정
• (id,name,phone) : 컬럼명 지정

conventional path load

• SQL문 INSERT문을 사용하여 데이터를 로드하는 방식
• 데이터 버퍼 캐시를 사용하여 데이터를 입력
• commit을 사용하여 영구적으로 데이터 반영
• 항상 redo entry 생성
• 모든 제약 조건 검사 수행
• 데이터 품질은 좋지만 수행 시간이 느림
• 다른 세션에서 대상 테이블에 DML 수행 가능
• 메모리 사용률과 리소스가 많이 발생, 입력 속도가 좋지 않음
• insert trigger 가 있을 경우 수행됨

sqlldr hr/hr control=insa.ctl

conventional path load 실습

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key,
    name varchar2(30),
    phone varchar2(20))
tablespace users;

select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';
select index_name, uniqueness, status from dba_indexes where owner = 'HR' and table_name = 'TEST';

데이터 파일, 컨트롤 파일 생성

[oracle@ora19c ~]$ mkdir data
[oracle@ora19c ~]$ cd data

# 데이터 파일 생성
[oracle@ora19c data]$ vi insa.dat
[oracle@ora19c data]$ cat insa.dat
1,"JAMES","010-1000-0000"
2,"GRACE","010-7777-7777"
3,"SCOTT","010-8888-8888"
3,"LUCAS","010-1004-1004"

# 컨트롤 파일 생성
[oracle@ora19c data]$ vi insa.ctl
[oracle@ora19c data]$ cat insa.ctl
load data
infile insa.dat
insert
into table hr.test
fields terminated by ',' optionally enclosed by '"'
(id,name,phone)

conventional path load

[oracle@ora19c data]$ sqlldr hr/hr control=insa.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 15 17:24:51 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 4

Table HR.TEST:
  3 Rows successfully loaded.

Check the log file:
  insa.log
for more information about the load.

[oracle@ora19c data]$ ls
insa.bad  insa.ctl  insa.dat  insa.log

문제가 발생하여 입력되지 않은 데이터

[oracle@ora19c data]$ cat insa.bad
3,"LUCAS","010-1004-1004"

로드 시 발생한 로그 정보

[oracle@ora19c data]$ cat insa.log

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 15 17:24:51 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Control File:   insa.ctl
Data File:      insa.dat
  Bad File:     insa.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table HR.TEST, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,  O(") CHARACTER
NAME                                 NEXT     *   ,  O(") CHARACTER
PHONE                                NEXT     *   ,  O(") CHARACTER

Record 4: Rejected - Error on table HR.TEST.
ORA-00001: unique constraint (HR.TEST_ID_PK) violated

Table HR.TEST:
  3 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                 193500 bytes(250 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Thu Jan 15 17:24:51 2026
Run ended on Thu Jan 15 17:24:52 2026

Elapsed time was:     00:00:00.49
CPU time was:         00:00:00.12

로드 된 데이터 조회

select * from hr.test;

direct path load

• 데이터 버퍼 캐시를 거치지 않고 대상 테이블에 데이터를 로드 하는 방식
• DATA SAVE를 사용(commit 하지 않음)
• 특정 조건에서만 redo 생성
• redo를 생성하지 않으려면 unrecoverable 사용
• 제약 조건 무시
• insert trigger를 수행하지 않음
• 다른 세션에서 대상 테이블에 대해 DML 수행 불가(Lock 발생)

sqlldr hr/hr control=insa.ctl direct=true

direct path load 실습

drop table hr.test purge;

create table hr.test(
    id number constraint test_id_pk primary key,
    name varchar2(30),
    phone varchar2(20))
tablespace users;

select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';
select index_name, uniqueness, status from dba_indexes where owner = 'HR' and table_name = 'TEST';

데이터 파일, 컨트롤 파일 생성

# 데이터 파일 확인
[oracle@ora19c data]$ cat insa.dat
1,"JAMES","010-1000-0000"
2,"GRACE","010-7777-7777"
3,"SCOTT","010-8888-8888"
3,"LUCAS","010-1004-1004"

# 컨트롤 파일 수정
[oracle@ora19c data]$ vi insa.ctl
[oracle@ora19c data]$ cat insa.ctl
unrecoverable load data
infile insa.dat
insert
into table hr.test
fields terminated by ',' optionally enclosed by '"'
(id,name,phone)

direct path load

[oracle@ora19c data]$ sqlldr hr/hr control=insa.ctl direct=true

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jan 15 17:44:47 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 4.

Table HR.TEST:
  4 Rows successfully loaded.

Check the log file:
  insa.log
for more information about the load.

로드 된 데이터 조회

• 제약 조건을 무시하고 데이터가 모두 입력됨

 select * from hr.test;

제약 조건 및 인덱스 문제 발생

• 제약 조건 상태가 disable novalidate 상태로 바뀜

select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';

• 인덱스도 unusable 상태로 바뀜

select index_name, uniqueness, status from dba_indexes where owner = 'HR' and table_name = 'TEST';

• 인덱스가 unusable 상태가 되면 dml 불가

insert into hr.test(id, name, phone) values(5, 'oracle', '010-0001-0001');

명령의 199 행에서 시작하는 중 오류 발생 -
insert into hr.test(id, name, phone) values(5, 'oracle', '010-0001-0001')
오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다

delete from hr.test where name = 'LUCAS';

명령의 200 행에서 시작하는 중 오류 발생 -
delete from hr.test where name = 'LUCAS'
오류 보고 -
ORA-01502: 인덱스 'HR.TEST_ID_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다

• 문제 되는 인덱스 삭제

drop index hr.test_id_pk;

문제 있는 데이터 수정 작업

truncate table hr.exceptions;
alter table hr.test enable validate constraint test_id_pk exceptions into hr.exceptions;
select * from hr.exceptions;

• SCOTT과 LUCAS의 id(pk)가 동일함

select rowid, id, name, phone from hr.test where rowid in (select row_id from hr.exceptions);

• LUCAS의 id를 4로 변경

update hr.test set id = 4 where rowid = 'AAASKwAAHAAAAHDAAD';
commit;
select * from hr.test;

제약 조건을 enable validate 상태로 변경

truncate table hr.exceptions;
alter table hr.test enable validate constraint test_id_pk exceptions into hr.exceptions;

# 문제 없음
select * from hr.exceptions;

select constraint_name, status, validated, deferrable, deferred from dba_constraints where owner = 'HR' and table_name = 'TEST';

select index_name, uniqueness, status from dba_indexes where owner = 'HR' and table_name = 'TEST';