How to import data to oracle database using SQL * loader, loaderoracle

How to import data to oracle database using SQL * loader, loaderoracle

When you need to import massive data into the database, the efficiency of using the graphical interface tool is very low. Here we will briefly introduce how to import the database using SQL * loader.

First, observe the table structure. If the current user does not have a data table available, create a data table with permissions.

Prepare a text file. Each row corresponds to a record of the data table, separated by spaces (or separated by commas, but all data must be consistent)

If the metadata contains an excel table, you can convert the excel file to a. csv file. Note that the default delimiter for converting the excel file to a csv file is a comma.

Write a control file with the extension:. ctl

Enter the dos window (win + r, cmd, and press Enter), and enter the command

Sqlldr userid = hr/123456 control = C: \ Users \ lujx01 \ Desktop \ iqms_ SQL \ testSQL. ctl

Userid indicates the user name and logon password of the data table owner.

Control indicates the absolute path of the control file (you can also use the relative path)

Log on to the hr user with sqlplus, query the data table, display the data, and import the data successfully. Here, the import method is append, which can be understood as splicing. That is, add the data in the original data table. If replace is used, this is to replace all the original data. If the default insert method is used, the data table must be empty.

A log file with the. log extension is stored in the current directory, which records the sqlloader operation process.