Generate 5 million pieces of large data in Oracle (use the sqlldr import command *. CTL file to import data)

 

Import data to Oracle

 

When importing data in Oracle, the file suffix is *. CTL

 

The command is sqlldr.

 

Sqlldr username/password control = 'tbl _ EMP, CTL'

 

Export part of data from postgre

Psql Saison-C 'select user_id, user_name from user order by 1, 2 'user_list.txt-a-f,-T

 

Generated file user_list.txt

100001, Xiaoming

100002, Xiaowang

 

CTL File

Load data

Infile user_list.txt

Append into Table tbl_emp

Filds terminated ","

Trailing nullcols

(

User_unique_key sequence (max ),

User_id,

User_name,

User_pin_id ": user_id ",

User_berthday "20120202 ",

Pay_month 10000,

User_flag "1"

)

 

 

Note: ①

The program has two databases, one of which is postgre and the other is Oracle.

After data is generated in postgre, The user_id user_name associated with the two databases is exported.

 

The database table in postgre is user

The database table in Oracle is tbl_emp.

 

---------------------------

Note: ②

The role of sequence (max)

Sequence is not used here. The function here is to auto-increment this field.

 

For example, this field is a char 10

 

The generated field is

1000000000

1000000001

1000000002

..

..

..

 

Note: ①

When running this command in Linux

You need to set the environment variable ORACLE_HOME.

Export ORACLE_HOME =/u01/APP/Oracle // product/10.2/db_1

 

Otherwise, an error is reported.

 

Message 2100 not found; no message file for Product = RDBMS, facility = ulmessage 2100 not found; no message file for Product = RDBMS, facility = ul

 

Note: ②

Oracle_sid also needs to be configured

 

 

Note ③

Run this command in the directory where the CTL file is located