Oracle_ using CTL files for bulk Import

The use of Oracle control files to enable storage of large quantities of data is much more efficient than SQL script files.

The steps for using the CTL control file are as follows:

1. Making a CTL file Test.ctl

Load data

infile ' d:\test.txt ' --data file, data source file

Append into table table_name--target table for data file import

Fields terminated by "@[email protected]"--field separators, we recommend special characters as field separators

Optionally enclosed by ' "'--delimiter

Trailing Nullcols--Allow skipping columns

( col1, col2, col3, Col4) fields in---table

----above italic part of the text, please replace according to the actual situation

2. Making a data source file

There is only data in the file and data columns are separated by "@[email protected" (you can export the data with a command or tool and then edit it via UE)

3. Execute the CTL file

Command line cmd execution, CD to Oracle control file directory,

Execute command: Sqlldr oracle/[email protected] control =test.ctl, errors=50000, load=450000

4. Implementation results

The result of the execution will be the log file (Input.log) corresponding to the name of the control file generated under Oracle's control file, which details the results of the operation.

5. When loading large amounts of data (approximately 10GB), it is best to suppress the generation of logs:

Sql>alter TABLE resultxt nologging;

This does not produce redo LOG, which can improve efficiency

---------------------------Example---------------------------------------------

--November 26, 2014 Wednesday 10:25 12 SEC--z_org.ctl
--Loading data into table z_org
Load data
InFile ' Z_org.txt '--Data file
Badfile ' Z_org.bad '
Replace into table z_org-Replaces table z_org content
Fields terminated by ' @[email protected]'--field delimiter
TRAILING nullcols---allow skipping columns
(
Gather_date Date "Yyyy-mm-dd",
Xaccount,
Name,
Sms_frees,
Freesign,
Sms_lowamt,
SMS_LOWAMX,
MP_L_LMT,
Mp_auths,
Mpausign,
MP_REM_PPL,
Mpremsig,
Mp_bal,
Mpbalsig,
Cal_limit,
Cal_auths,
Cal_auths_flag,
Cal_bal,
Cal_bal_flag,
CAL_REMPPL,
Cal_remppl_flag,
Sms_freeyn,
BAL_MPPL,
BAL_MPPLX,
BAL_L_MPPL,
Daily_rep,
Product,
)

Oracle_ using CTL files for bulk Import