Oracle high-speed Batch Data Loading tool SQL * loader instructions

Source: Internet
Author: User
Tags dname

SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. This is a very useful tool for loading data to the Oralce database in multiple flat file formats. SQLLDR can load a large amount of data in a very short time. It has two operation modes.
Traditional path(Conventional path): SQLLDR uses SQL insert to load data for us.
Direct path: In this mode, SQLLDR directly formats database blocks instead of SQL.
Using 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, it may also avoid redo generation. To fully load data in a database without any data, the fastest way is to load data using parallel direct paths.

To use SQLLDR, you must have a control file ). The control file contains information describing the input data (such as the layout and type of the input data), as well as information about the target table. Control files can even contain the data to be loaded.

1. Create a new control file demo1.ctl with the following content:
Copy codeThe Code is as follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
Fields terminated ','
(DEPTNO, DNAME, LOC)
BEGINDATA
10, Sales, Virginia
20, Accounting, Virginia
30, Consulting, Virginia
40, Finance, Virginia

Load data: This will tell SQLLDR what to do (in this example, it indicates the DATA to be loaded ). 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, for example, 6th ~ 10 rows. You can also specify the file name of another file that contains data.
Into table dept: This tells SQLLDR which TABLE to load the data to (in this example, the data is loaded to the dept table ).
Fields terminated by ',': This tells SQLLDR that the data format should be separated BY commas.
(DEPTNO, DNAME, LOC): This tells SQLLDR the column to be loaded, the sequence of these columns in the input data, and the data type. This refers to the data type in the input stream, not in the database. In this example, the Data Type of the column is CHAR (255) by default, which is sufficient.
BEGINDATA: This will tell SQLLDR that you have completed the description of the input data, followed by the rows (7th ~ 10 rows) is the specific data to be loaded to the DEPT table.
2. Create an empty table dept
Copy codeThe Code is as follows:
Create table dept
(Deptno number (2) constraint dept_pk primary key,
Dname varchar2 (14 ),
Loc varchar2 (13)
)
/

3. load data
Copy codeThe Code is as follows:
Sqlldr userid = lwstest/netjava control = d: \ demo1.ctl

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.