Oracle High Speed Bulk Data Loading Tool sql*loader Instructions for Use
- 2021-06-28 14:26:52
- OfStack
SQL*Loader (SQLLDR) is a high-speed bulk data loading tool for Oracle.This is a very useful tool for loading data into Oralce databases in a variety of flat file formats.SQLLDR can load a large amount of data in a very short time.It has two modes of operation.
Traditional Path: (conventional path): SQLLDR will use SQL insert to load data for us.
Direct Path (direct path): In this mode, SQLLDR does not use SQL;Instead, format the database block directly.
With direct path loading, you can read data from a flat file and write it directly to a formatted database block, bypassing the entire SQL engine and undo generation, and possibly avoiding redo generation.The fastest way to fully load data in a database without any data is to use parallel direct path loading.
To use SQLLDR, you need a control file (control file).The control file contains information describing the input data, such as its layout, data type, and so on, as well as information about the target table.The control file can even contain data to load.
1. Create a new control file, demo1.ctl, which reads as follows:
LOAD DATA: This tells SQLLDR what to do (in this case, to load data).SQLLDR can also execute CONTINUE_LOAD, that is, continue loading.
INFILE *: This tells SQLLDR that the data to be loaded is actually contained in the control file itself, as shown in lines 6-10.You can also specify the filename of another file that contains data.
INTO TABLE DEPT: This tells SQLLDR which table to load the data into (in this case, the data into the DEPT table).
FIELDS TERMINATED BY',': This tells SQLLDR that the data should be in comma-separated values.
(DEPTNO, DNAME, LOC): This tells SQLLDR what columns to load, the order in which they are in the input data, and the data type.This refers to the data type of the data in the input stream, not the data type in the database.In this example, the column's data type defaults to CHAR (255), which is sufficient.
BEGINDATA: This tells SQLLDR that you have finished describing the input data and that the next rows (rows 7-10) are the specific data to be loaded into the DEPT table.
2. Create an empty table dept
3. Loading data
Traditional Path: (conventional path): SQLLDR will use SQL insert to load data for us.
Direct Path (direct path): In this mode, SQLLDR does not use SQL;Instead, format the database block directly.
With direct path loading, you can read data from a flat file and write it directly to a formatted database block, bypassing the entire SQL engine and undo generation, and possibly avoiding redo generation.The fastest way to fully load data in a database without any data is to use parallel direct path loading.
To use SQLLDR, you need a control file (control file).The control file contains information describing the input data, such as its layout, data type, and so on, as well as information about the target table.The control file can even contain data to load.
1. Create a new control file, demo1.ctl, which reads as follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
LOAD DATA: This tells SQLLDR what to do (in this case, to load data).SQLLDR can also execute CONTINUE_LOAD, that is, continue loading.
INFILE *: This tells SQLLDR that the data to be loaded is actually contained in the control file itself, as shown in lines 6-10.You can also specify the filename of another file that contains data.
INTO TABLE DEPT: This tells SQLLDR which table to load the data into (in this case, the data into the DEPT table).
FIELDS TERMINATED BY',': This tells SQLLDR that the data should be in comma-separated values.
(DEPTNO, DNAME, LOC): This tells SQLLDR what columns to load, the order in which they are in the input data, and the data type.This refers to the data type of the data in the input stream, not the data type in the database.In this example, the column's data type defaults to CHAR (255), which is sufficient.
BEGINDATA: This tells SQLLDR that you have finished describing the input data and that the next rows (rows 7-10) are the specific data to be loaded into the DEPT table.
2. Create an empty table dept
create table dept
( deptno number(2) constraint dept_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
3. Loading data
sqlldr userid=lwstest/netjava control=d:\demo1.ctl