Solutions for importing TXT file data from ORACLE

Scenario:

Data.txt source data:

[INFO] 00:01:17 1610 FHR "row number = 24. view the volume data of the specified plan. plan No. = 121200102. "[INFO] 00:03:13 1610 FHR" row number = 24. view the volume data of the specified plan. plan No. = 121200103. "[INFO] 00:20:21 7362 RICC" IntervalTime = 0, RollingTime = 0 "[INFO] 2012-12-01 00:20:21 7363 RICC" WRTFMOff: 24.5, 24.8, 24.9, 25.1, 25.3, 25.3, 25.4, 26.5 [C]. "[INFO] 00:20:21 7362 RICC" IntervalTime = 0, RollingTime = 0, WRTFMOff: fT = 24.632685"

After obtaining the source data, we can see at first glance that the SQL * loader tool should be used for data of this type. Import the data from the source file to the oracle database.

Then, let's familiarize ourselves with this tool:
I. SQL * loader Overview

In many cases, our data needs to be transferred to the database in batches or between databases in batches. Common
The scenario is to use the data extracted from the transaction processing system to fill the data warehouse, or copy the data from the real-time system to the test and development environment. Pair
For large-scale operations, it is not always the best way to INSERT Data using standard INSERT statements. oracle itself comes with the SQL * Loader and Data Pump functions to support batch operations. By using external tables, you can read data without inserting data into the database.
In terms of architecture, the SQL * Loader process is similar to other user processes: It connects to the database through the server process.
To insert a row, you can use two techniques: regular mode or direct path reading. The general method is to use INSERT. SQL * The Loader user process constructs an insert statement containing the bound variables in the values clause, then reads the source data file and executes the statement for each row to be inserted.
Insert once. This method uses the database buffer cache and generates undo and redo data redo logs.
Statements are similar to other similar statements, and data persistence is achieved through normal submission and processing.
The direct path avoids the database buffer. SQL * loader reads the source data file and sends the content to the server process.
Then, the server process assembles blocks in the table data in its PGA and writes them directly to the data file. High level of table write operations
Online completion, called data save for data storage ). A high watermark is a mark in a table segment, and no data is written to it:
The online space is allocated to unused tables. After loading, the SQL * loader moves the high level line to include the latest
The data block to be written, and other users can immediately see the rows in the data block. The preceding operation is equivalent to a commit command.
At this time, undo logs are not generated, or you can manually cancel the generation of redo logs. Therefore, direct path loading is fast.
Ii. disadvantages of SQL * Loader direct path
Direct paths also have the following Disadvantages:
1. During the operation, the reference Integrity Constraint must be deleted or disabled. Only the unique, primary key, and not null constraints can be implemented ).
2. DML lock tables for other sessions
3. insert trigger not activated
4. The cluster Table cannot be used.

 

SQL * loader architecture:

650) this. width = 650; "title =" 3.jpg" src = "http://www.bkjia.com/uploads/allimg/131228/2209525938-0.jpg"/>

Iii. Actual Operation demonstration

Well, there is no way to elaborate on it here. You can add relevant knowledge.

Create a TEST table (we want to import the source data to this table ):
SQL> conn hr/oracle @ testdb

SQL> create table test (
2 name varchar2 (10 ),
3 indate varchar2 (20 ),
4 intime varchar2 (20 ),
5 num int,
6 source varchar2 (10 ),
7 description varchar2 (128)
8 );

Prepare data.txt and data_test.ctl (load the control file to be used and define data rules)
Data_test.ctl:

LOAD DATAINFILE "E:\oracle\exercises\data.txt"TRUNCATE INTO TABLE TESTFIELDS TERMINATED BY " " optionally enclosed by '"'(NAME,INDATE,INTIME,NUM,SOURCE,DESCRIPTION)

 

Sqlldr is the SQL * loader tool, which is in the $ ORACLE_HOME/bin directory.
C: \ Documents and Settings \ Administrator> sqlldr hr/oracle @ testdb control = E: \ oracle \ exercises \ data_test.ctl log = E: \ oracle \ exercises \ data_test.log

650) this. width = 650; "style =" float: none "title =" 1.jpg" src = "http://www.bkjia.com/uploads/allimg/131228/2209522933-1.jpg"/>

Upload 3 hours ago Download Attachment(39.43 KB)

Query Result:

650) this. width = 650; "style =" float: none "title =" 2.jpg" src = "http://www.bkjia.com/uploads/allimg/131228/2209525b4-2.jpg"/>

Upload 3 hours ago Download Attachment(39.34 KB)

So far, the demand has been completed. What do the commands in the control file mean? If you have time, explain it next time or leave some time to learn for yourself. I hope you can participate in the discussion and improve yourself!

 

This article is from the "Haibin technology blog". For more information, contact the author!