Sqlldr batch import and export data test

Sqlldr is a recommended method for processing large data volumes. It has many performance switches to minimize redo, undo generation, and control data processing methods.

Sqlldr is a recommended method for processing large data volumes. It has many performance switches to minimize redo, undo generation, and control data processing methods.

Sqlldr is a recommended method for processing large data volumes. It has many performance switches to minimize redo and undo generation, control Data Processing Methods (insert, append, replace, truncate)

Because the project needs to compare the performance of datapump, it is still not ideal, so we still want to use sqlldr. I did a simple test.

According to thomas kyte, the fastest way to load parallel execution paths is to write only formatted data blocks, minimizing redo and undo generation.

A csv file in sqlldr in Linux

Sqlldr Load file, ORA-01722: invalid number Solution

Oracle sqlldr imports text ending with carriage return and carriage return

Sqlldr import garbled Oracle client Character Set

Use default values to import data from Oracle sqlldr and improve performance

First, write the following script. Metadata can be dynamically generated from a user's table.

Sqlplus-s $1 < Set pages 0
Col object_name format a30
Set linseize 10000
Set feedback off
Set colsep ','
Spool $ 2.lst
Select * from $2;
Spool off;
EOF

The data generated after running is roughly as follows.
[Ora11g @ rac1 sqlldr] $ ksh spooldata. sh n1/n1 t
370753,102 05, KU $ _ DOMIDX_OBJNUM_VIEW, VIEW
370754,102 07, KU $ _ OPTION_OBJNUM_T, TYPE
370755,102 08, KU $ _ EXPREG, VIEW
370756,102 10, SYS_YOID0000010209 $, TYPE
370757,102 09, KU $ _ OPTION_OBJNUM_VIEW, VIEW
370758,102 11, KU $ _ OPTION_VIEW_OBJNUM_VIEW, VIEW
370759,102 12, KU $ _ MARKER_T, TYPE
370760,102 14, SYS_YOID0000010213 $, TYPE
370761,102 13, KU $ _ MARKER_VIEW, VIEW
370762,102 15, KU $ _ TABPROP_VIEW, VIEW
370763,102 16, KU $ _ PFHTABPROP_VIEW, VIEW
370764,102 17, KU $ _ REFPARTTABPROP_VIEW, VIEW
370765,102 18, KU $ _ MVPROP_VIEW, VIEW
370766,102 19, KU $ _ MVLPROP_VIEW, VIEW
370767,102 20, KU $ _ TTS_VIEW, VIEW
370768,102 21, KU $ _ TAB_TS_VIEW, VIEW
370769,102 22, KU $ _ TTS_IND_VIEW, VIEW
370770,102 23, KU $ _ IND_TS_VIEW, VIEW
370771,102 24, KU $ _ CLU_TS_VIEW, VIEW

Then prepare the control file sqlldr. ctl and load the data from t to tt.
Load data
Into table tt
Fields terminated ','
(Id, object_id, object_name, object_type)

Try to import:
[Ora11g @ rac1 sqlldr] $ sqlldr n1/n1 control = sqlldr. ctl data = t. lst
SQL * Loader: Release 11.2.0.3.0-Production on Tue May 27 08:09:25 2014
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
However, there is no feedback.
View automatically generated sqlldr. log
There are the following errors.

Column Name Position Len Term Encl Datatype
--------------------------------------------------------------------------
Id first *, CHARACTER
OBJECT_ID NEXT *, CHARACTER
OBJECT_NAME NEXT *, CHARACTER
OBJECT_TYPE NEXT *, CHARACTER

Record 1: Rejected-Error on table TT, column OBJECT_TYPE.
Field in data file exceeds maximum length
Record 2: Rejected-Error on table TT, column OBJECT_TYPE.
Field in data file exceeds maximum length
Record 3: Rejected-Error on table TT, column OBJECT_TYPE.
Field in data file exceeds maximum length
Record 4: Rejected-Error on table TT, column OBJECT_TYPE.
Field in data file exceeds maximum length

After trying for a while, we finally found that the length setting for the set linesize was relatively large. when parsing Based on commas, the length of the last field contains the remaining space. When it is loaded, it will be found that the length is too large. The length of the table is exceeded.
In this case, I cannot specify the length one by one.
At this time, I thought of the trimspool function and tried to make it work.
The spooldata. sh script content is as follows:
Sqlplus-s $1 < Set pages 0
Col object_name format a30
Set linesize 10000
Set trimspool on
Set feedback off
Set colsep ','
Spool $ 2.lst
Select * from $2 where rownum <20;
Spool off;
EOF