- SQL*Loader express mode
The main benefit of SQL*Loader express mode is the savings for time and effort that
results from not needing to write and test a SQL*Loader control file. Instead, you specify
a single SQL*Loader command with a few parameters and the load starts.
Another benefit of express mode is that it will try to use the fastest mechanism for loading
data files: external tables using parallel inserts with the append hint. The append hint on
an insert statement tells the database to use direct path for loading table data. Executing
the insert in parallel means that multiple processes can load data from the data file at
once, reducing the elapsed time for the load.
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 this file "emp_x.dat" to /u01/app/oracle
# cat emp_x.dat
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
...
# now do this command : sqlldr hr/hr@orclpdb TABLE=emp_x
# oracle will look to any file called emp_x.dat in same directory
/*
SQL*Loader first created a temporary external table, used the external table
to load the content of the
external data file into the table, and finally dropped the temporary external table.
*/
[oracle@test oracle]$ sqlldr hr/hr@orclpdb TABLE=emp_x
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 17:04:11 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP_X
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table EMP_X:
300 Rows successfully loaded.
Check the log files:
emp_x.log
emp_x_%p.log_xt
for more information about the load.
[oracle@test oracle]$ pwd
/u01/app/oracle
[oracle@test oracle]$ ll
-rw------- 1 oracle oinstall 5184 12월 25 17:01 emp_x.dat
-rw-r--r-- 1 oracle oinstall 2626 12월 25 17:04 emp_x.log
-rw-r--r-- 1 oracle oinstall 951 12월 25 17:04 emp_x_3759.log_xt
...
*/
select * from emp_x
/*
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
....
*/
delete from emp_x;
commit;
/*
300 rows deleted.
Commit complete.
*/
# test 1
# pk constraint, uk,fk,check, not null
# make all the test on id 280 --- follow with me
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_C007640 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
*/
# test 2, Path used:External Table
# make the length for name more than 10char
# follow with me
delete from emp_x;
commit;
[oracle@test oracle]$ sqlldr hr/hr@orclpdb TABLE=emp_x
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 18:06:22 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP_X
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table EMP_X:
299 Rows successfully loaded. <<<<<<< # 오류가 발생하여 1개의 라인이 스킵됨.
Check the log files:
emp_x.log
emp_x_%p.log_xt
for more information about the load.
[oracle@test oracle]$
# test 3, Path used:Conventional
# sqlldr hr/hr@orclpdb TABLE=emp_x DIRECT=NO
# this will use the Conventional Load --not external table
delete from emp_x;
commit;
/*
299 rows deleted.
Commit complete.
*/
[oracle@test oracle]$ sqlldr hr/hr@orclpdb TABLE=emp_x DIRECT=NO
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 18:08:27 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP_X
Path used: Conventional
Commit point reached - logical record count 250
Commit point reached - logical record count 300
Table EMP_X:
299 Rows successfully loaded.
Check the log file:
emp_x.log
for more information about the load.
[oracle@test oracle]$
select * from emp_x order by 1;
/*
EMPID NAME G DEPTNO ADDRESS
---------- ---------- - ---------- ----------
2 KH2 M 1 NA
3 KH3 M 1 NA
4 KH4 M 1 NA
5 KH5 M 1 NA
....
*/
# test 4, Path used: Direct
# sqlldr hr/hr@orclpdb TABLE=emp_x DIRECT=YES
# this will use the DIRECT PATH LOAD
delete from emp_x;
commit;
/*
299 rows deleted.
Commit complete.
*/
[oracle@test oracle]$ sqlldr hr/hr@orclpdb TABLE=emp_x DIRECT=YES
SQL*Loader: Release 19.0.0.0.0 - Production on Mon Dec 25 18:55:50 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP_X
Path used: Direct
Load completed - logical record count 300.
Table EMP_X:
300 Rows successfully loaded.
Check the log file:
emp_x.log
for more information about the load.
[oracle@test oracle]$
SELECT * FROM emp_x
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
....
*/
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_C007640 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
*/
'Database > Oracle' 카테고리의 다른 글
| 116 - [Oracle 19C] Enableing ARCHIVELOG mode. (0) | 2023.12.28 |
|---|---|
| 115 - [Oracle 19C] External table with PARTITIONS (0) | 2023.12.27 |
| 113 - [Oracle 19C] SQL*Loader Loading Methods - Direct Path Load (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 |