- Direct Path Load (DIRECT=TRUE)
Uses data saves (faster operation)
Generates redo only under specific conditions
Enforces only PRIMARY KEY, UNIQUE, and NOT NULL
Does not fire INSERT triggers
Does not load into clusters
Prevents other users from making changes to tables during load operation
show user;
show con_name;
/*
USER is "HR"
CON_NAME
------------------------------
ORCLPDB
*/
create table dept_x
(deptno number,
dname varchar2(100),
constraint dept_x primary key (deptno)
);
/*
Table DEPT_X created.
*/
insert into dept_x values (1, 'sales');
commit;
/*
1 row inserted.
Commit complete.
*/
select * from dept_x;
/*
DEPTNO DNAME
---------- ---------------
1 sales
*/
create table emp_x
( empid number,
name varchar2(10) not null,
gender varchar(1),
deptno number,
address varchar2(100),
constraint emp_x_pk primary key (empid),
constraint emp_x_uk1 unique (name),
constraint emp_x_fk1 foreign key (deptno) references dept_x(deptno),
constraint emp_x_chq1 check ( gender in ('M','F') )
);
/*
Table EMP_X created.
*/
select * from emp_x;
/*
no rows selected
*/
# MOVE THE FILE EMPLOYEES.csv TO /u01/app/oracle
# we need to do a file called conrol file .ctl ( example EMPLOYEES.ctl )
/*
Load Data
INFILE '/u01/app/oracle/EMPLOYEES.csv'
APPEND
INTO Table emp_x
FIELDS TERMINATED BY ','
(empid,
name,
gender,
deptno,
address
)
*/
[oracle@test oracle]$ cat EMPLOYEES.csv | more
1,KH1,M,1,NA
2,KH2,M,1,NA
3,KH3,M,1,NA
4,KH4,M,1,NA
5,KH5,M,1,NA
6,KH6,M,1,NA
7,KH7,M,1,NA
8,KH8,M,1,NA
9,KH9,M,1,NA
10,KH10,M,1,NA
....
[oracle@test oracle]$ cat EMPLOYEES.ctl
Load Data
INFILE '/u01/app/oracle/EMPLOYEES.csv'
APPEND
INTO Table emp_x
FIELDS TERMINATED BY ','
(empid,
name,
gender,
deptno,
address
)
# TEST 1 ( NO wrong data ( length or invalid constraints )
[oracle@test oracle]$ sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log DIRECT=TRUE
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 01:40:16 2023
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 300.
Table EMP_X:
300 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
SQL> select * from emp_x where rownum<10 order by 1;
EMPID NAME G DEPTNO ADDRESS
---------- ---------- - ---------- ----------
1 KH1 M 1 NA
2 KH2 M 1 NA
3 KH3 M 1 NA
4 KH4 M 1 NA
5 KH5 M 1 NA
6 KH6 M 1 NA
7 KH7 M 1 NA
8 KH8 M 1 NA
9 KH9 M 1 NA
....
select OWNER, CONSTRAINT_NAME, table_name, SEARCH_CONDITION, status from user_constraints
where table_name='EMP_X';
/*
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION STATUS
---------- --------------- --------------- ------------------------- --------
HR SYS_C007634 EMP_X "NAME" IS NOT NULL ENABLED
HR EMP_X_PK EMP_X ENABLED
HR EMP_X_UK1 EMP_X ENABLED
HR EMP_X_FK1 EMP_X DISABLED
HR EMP_X_CHQ1 EMP_X gender in ('M','F') DISABLED
*/
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_CHQ1;
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_FK1;
/*
Table EMP_X altered.
Table EMP_X altered.
*/
select OWNER, CONSTRAINT_NAME, table_name, SEARCH_CONDITION, status from user_constraints
where table_name='EMP_X';
/*
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION STATUS
---------- --------------- --------------- ------------------------- --------
HR EMP_X_FK1 EMP_X ENABLED
HR SYS_C007634 EMP_X "NAME" IS NOT NULL ENABLED
HR EMP_X_CHQ1 EMP_X gender in ('M','F') ENABLED
HR EMP_X_PK EMP_X ENABLED
HR EMP_X_UK1 EMP_X ENABLED
*/
select index_name, index_type, table_name, status, constraint_index from user_indexes
where table_name='EMP_X'
/*
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS CONSTRAINT_INDE
--------------- --------------- --------------- -------- ---------------
EMP_X_PK NORMAL EMP_X VALID YES
EMP_X_UK1 NORMAL EMP_X VALID YES
*/
truncate table emp_x
/*
Table EMP_X truncated.
*/
--TEST 2 FK AND check CONSTRAINTS
/*
데이터를 아래와 같이 수정합니다
287,KH287,x,1,NA
288,KH288,M,2,NA
*/
[oracle@test oracle]$ sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log DIRECT=TRUE
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 01:53:08 2023
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 300.
Table EMP_X:
300 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
select OWNER, CONSTRAINT_NAME, table_name, SEARCH_CONDITION, status from user_constraints
where table_name='EMP_X';
/*
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION STATUS
---------- --------------- --------------- ------------------------- --------
HR SYS_C007634 EMP_X "NAME" IS NOT NULL ENABLED
HR EMP_X_PK EMP_X ENABLED
HR EMP_X_UK1 EMP_X ENABLED
HR EMP_X_FK1 EMP_X DISABLED
HR EMP_X_CHQ1 EMP_X gender in ('M','F') DISABLED
*/
# 데이터 체크 오류 및 외부 키 오류가 발생하여 alter 처리가 되지 않음.
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_CHQ1;
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_FK1;
/*
Error starting at line : 267 in command -
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_CHQ1
Error report -
ORA-02293: cannot validate (HR.EMP_X_CHQ1) - check constraint violated
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
Error starting at line : 268 in command -
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_FK1
Error report -
ORA-02298: cannot validate (HR.EMP_X_FK1) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious
*/
truncate table emp_x
/*
Table EMP_X truncated.
*/
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_CHQ1;
ALTER TABLE emp_x ENABLE CONSTRAINT EMP_X_FK1;
/*
Table EMP_X altered.
Table EMP_X altered.
*/
# TEST 3
# 아래와 같이 데이터를 수정한다
/*
--PK, NOT NULL ,uk
MAKE THE ID 289=1
290,,M,1,NA
291,KH1,M,1,NA
*/
SELECT * FROM emp_x --where rownum<10
ORDER BY 1;
/*
EMPID NAME G DEPTNO ADDRESS
---------- ---------- - ---------- --------------------
1 KH289 M 1 NA
1 KH1 M 1 NA
2 KH2 M 1 NA
3 KH3 M 1 NA
4 KH4 M 1 NA
....
*/
SELECT * FROM emp_x
ORDER BY 2;
/*
EMPID NAME G DEPTNO ADDRESS
---------- ---------- - ---------- --------------------
1 KH1 M 1 NA
291 KH1 M 1 NA
10 KH10 M 1 NA
100 KH100 M 1 NA
....
*/
[oracle@test oracle]$ cat EMPLOYEES.bad
290,,M,1,NA
[oracle@test oracle]$
select OWNER, CONSTRAINT_NAME, table_name, SEARCH_CONDITION, status from user_constraints
where table_name='EMP_X';
/*
OWNER CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION STATUS
---------- --------------- --------------- ------------------------- --------
HR SYS_C007634 EMP_X "NAME" IS NOT NULL ENABLED
HR EMP_X_FK1 EMP_X DISABLED
HR EMP_X_CHQ1 EMP_X gender in ('M','F') DISABLED
HR EMP_X_PK EMP_X DISABLED
HR EMP_X_UK1 EMP_X DISABLED
*/
# 아래 2개의 옵션은 보장이되지 않는 옵션이므로 자동으로 disabled 된다.
Constraint EMP_X.EMP_X_CHQ1 was disabled and novalidated before the load.
Constraint EMP_X.EMP_X_FK1 was disabled and novalidated before the load.
# null 옵션은 유지가 되므로 1개의 데이터가 리젝된다
Record 290: Rejected - Error on table EMP_X, column NAME.
ORA-01400: cannot insert NULL into (NAME)
# PK, UK 옵션은 중복된 데이터를 입력하려고 자동으로 disabled 되었
The following index(es) on table EMP_X were processed:
ORA-39828: Constraint EMP_X_PK was disabled because of index HR.EMP_X_PK error.
index HR.EMP_X_PK was made unusable due to:
ORA-01452: cannot CREATE UNIQU다E INDEX; duplicate keys found
ORA-39828: Constraint EMP_X_UK1 was disabled because of index HR.EMP_X_UK1 error.
index HR.EMP_X_UK1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
'Database > Oracle' 카테고리의 다른 글
| 115 - [Oracle 19C] External table with PARTITIONS (0) | 2023.12.27 |
|---|---|
| 114 - [Oracle 19C] SQL*Loader express mode (1) | 2023.12.27 |
| 112 - [Oracle 19C] SQL*Loader Loading Methods - Conventional Load (1) | 2023.12.25 |
| 111 - [Oracle 19C] SQL Loader - discard file example (0) | 2023.12.25 |
| 110 - [Oracle 19C] Moving data from PDB to PDB (0) | 2023.12.21 |