Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to load external data with SQL*Loader". In daily operation, I believe many people have doubts about how to load external data with SQL*Loader. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to load external data with SQL*Loader". Next, please follow the editor to study!

SQL*Loader can be used to bulk upload some datasets generated by third-party systems. DBA configures a SQL*Loader control file that interprets the contents of the input data file, and SQL*Loader can read the input file and insert the data into the Oracle database table.

SQL*Loader is a client-server process that can import data using two techniques: regular or direct.

The normal INSERT statement is used in the normal way. The SQL*Loader user process constructs an INSERT statement containing binding variables in the VALUES clause, then reads the source data file and executes the INSERT once for each row to be inserted. This method uses database buffer caching and generates undo and redo data.

The direct path avoids the database buffer cache. SQL*Loader reads the source data file and sends its contents to the server process. After that, the server process assembles blocks of table data in its PGA and writes them directly to the data file. The write operation is done on the high water mark of the table, which is called data preservation. When the load is complete, SQL*Loader moves the high watermark to include the most recently written blocks. No undo is generated at this time, so direct path loading is extremely fast.

Direct path loading is fast, but there are the following problems:

Referential integrity constraints and check constraints must be deleted or disabled during operation, and only unique, primary key and not null constraints can be implemented

The INSERT trigger will not be activated

DML lock table for other sessions

Cannot use direct path for cluster table

The above limitation exists because of the lack of interaction with SGA during loading.

SQL*Loader uses a variety of files:

Input data file (input datafile): the source data to be uploaded to the database.

Control file (control file): instructs SQL*Loader how to interpret the contents of the input file and how to extract records.

Log file (log file): outlines the success and failure of the job and the details of related errors.

Error file (bad file): a record that violates a rule and is not successfully inserted into the database is written to this file.

Obsolete file (discard file): records that are discarded because they are successfully extracted but do not match the selection criteria are written to this file.

Consider the following table:

SQL > desc dept

Name Type Nullable Default Comments

--

DEPTNO NUMBER (2)

DNAME VARCHAR2 (14) Y

LOC VARCHAR2 (13) Y

The source data file is e:\ dept.txt:

60,liuln,nanjing

70,shenzj,jiaxing

80,chenyz,shanghai

The SQL*Loader control file used to load this data is e:\ dept.ctl:

Load data

Infile'e:\ dept.txt'

Badfile'e:\ dept.bad'

Discardfile'e:\ dept.dsc'

Append

Into table scott.dept

Fields terminated by','

Trailing nullcols

(deptno integer external (2)

Dname

Loc)

Input files, error files, obsolete files are specified in the control file, table rows are added rather than truncated, tables and field separators are specified, trailing nullcols indicates that null values are inserted if fields are missing, and finally indicates the column corresponding to the inserted data.

To perform the load, execute the following command at the operating system prompt

Sqlldr scott/tiger control=e:\ dept.ctl log=e:\ dept.log direct=true

The direct=true parameter instructs SQL*Loader to use a direct path instead of the default regular insert operation. After the operation, you can check the generated log file information, while querying the table dept to confirm that the data has been added.

At this point, the study on "how to load external data with SQL*Loader" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.