- Conventional Load
Uses COMMIT
Always generates redo entries
Enforces all constraints
Fires INSERT triggers
Can load into clustered tables
Allows other users to modify tables during load operation
The insertion of an array ofrecords is followed by a COMMIT operation.
Each data load may involve several transactions.
SQL> show user;
USER is "HR"
SQL> show con_name
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.
*/
SQL> 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 )
[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
11,KH11,M,1,NA
....
[oracle@test oracle]$ pwd
/u01/app/oracle
[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 )
# sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
[oracle@test oracle]$ sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
SQL*Loader: Release 19.0.0.0.0 - Production on Sun Dec 24 23:41:48 2023
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 250
Commit point reached - 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.
[oracle@test oracle]$
select count(1) from emp_x;
/*
COUNT(1)
----------
300
*/
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
....
# TEST 2 updae some values and run sqlldr control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
/*
257,khaledalkhudari,M,1,NA
258,KH258,x,1,NA
259,KH259,M,2,NA
*/
truncate table emp_x;
/*
Table EMP_X truncated.
*/
[oracle@test oracle]$ sqlldr control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
Username:hr/hr@orclpdb
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 00:11:21 2023
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 250
Commit point reached - logical record count 300
Table EMP_X:
297 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
[oracle@test oracle]$ cat EMPLOYEES.bad
257,khaledalkhudari,M,1,NA
258,KH258,x,1,NA
259,KH259,M,2,NA
# test 3 updae some values
# 260,,M,1,NA
truncate table emp_x
/*
Table EMP_X truncated.
*/
[oracle@test oracle]$ sqlldr control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
Username:hr/hr@orclpdb
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 00:13:19 2023
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 250
Commit point reached - logical record count 300
Table EMP_X:
296 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
[oracle@test oracle]$ cat EMPLOYEES.bad
257,khaledalkhudari,M,1,NA
258,KH258,x,1,NA
259,KH259,M,2,NA
260,,M,1,NA
# test 4
# make the id 280 = 1
truncate table emp_x
/*
Table EMP_X truncated.
*/
[oracle@test oracle]$ sqlldr control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log
Username:hr/hr@orclpdb
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 00:46:12 2023
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 250
Commit point reached - logical record count 300
Table EMP_X:
295 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
[oracle@test oracle]$ cat EMPLOYEES.bad
257,khaledalkhudari,M,1,NA
258,KH258,x,1,NA
259,KH259,M,2,NA
260,,M,1,NA
1,KH280,M,1,NA
# test 5
# sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log ROWS=50
truncate table emp_x
/*
Table EMP_X truncated.
*/
[oracle@test oracle]$ sqlldr hr/hr@orclpdb control=/u01/app/oracle/EMPLOYEES.ctl log=/u01/app/oracle/EMPLOYEES.log ROWS=50
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 01:01:39 2023
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 50
Commit point reached - logical record count 100
Commit point reached - logical record count 150
Commit point reached - logical record count 200
Commit point reached - logical record count 250
Commit point reached - logical record count 300
Table EMP_X:
295 Rows successfully loaded.
Check the log file:
/u01/app/oracle/EMPLOYEES.log
for more information about the load.
[oracle@test oracle]$
[oracle@test oracle]$ cat EMPLOYEES.bad
257,khaledalkhudari,M,1,NA
258,KH258,x,1,NA
259,KH259,M,2,NA
260,,M,1,NA
1,KH280,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 EMP_X_FK1 EMP_X ENABLED
HR SYS_C007628 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
*/
'Database > Oracle' 카테고리의 다른 글
| 114 - [Oracle 19C] SQL*Loader express mode (1) | 2023.12.27 |
|---|---|
| 113 - [Oracle 19C] SQL*Loader Loading Methods - Direct Path Load (1) | 2023.12.27 |
| 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 |
| 109 - [Oracle 19C] External Table - ORACLE_DATAPUMP (0) | 2023.12.21 |
