Import large text (TXT) files to Oracle using SQLLDR

There are several ways to import text files into an Oracle database, but if you import a text file that is too large, such as a 5g,10g text file, there are some ways to do it, such as the import text feature in Plsql developer, and if the text file is too large, not only is the import slow, The middle is also prone to error. At this time Sqlldr will be able to work, SQLLDR can insert 5W per second to Oracle data, you can customize the control file, import control, and the import process is not easy to error, the following easy to record the import process.

1.SQLLDR Import Control File description

1 Load Data2InFile'/home/oracle/42.txt'                    //absolute location of text files3Append into table Doupoidx//the name of the table to import4Fields terminated by','  　　　　　　　　　　　　 //delimiter definition, here is the comma5Optionally enclosed by'"'  　　　　　　　　  //Filter "No.6TRAILING Nullcols//the order of the corresponding fields. 7 (8Time TimeStamp'YYYY-MM-DD Hh24:mi:ss',9 Price ,Ten volume, One Interest A)

2. Execute the import command under Oracle User

Sqlldr  userid=scott/[email protected] control =/home/oracle/input.ctl       // user name/ Password @ Instance name control= The location of the control file

Small test on optionally enclosed by

left without quotation marks to the right: successful, as-is import
111 "
222 ""
333 "" "

left with quotation marks to the right: failed, second enclosure string not present
"The
"" 555
"" " 666

left and right have equal odd number of quotes, success, quote number =2n+1 when importing data with N quotes
"777"
"" " 888 " ""

left and right have equal even number of quotes, failed, no Terminator found after TERMINATED and enclosed field
"" 999 ""
"" "" " AAA" "" "

An even number of odd sides on one side: failure, error message for both of the above
" BBB" "
" CCC "
"" DDD ""
" EEE" ""

even numbers with unequal sides: failed, no Terminator found after TERMINATED and enclosed field
"" FFF "" ""
"" "" GGG ""

odd numbers on both sides: success, quotes =2n+1 when importing data with N quotes
" HHH " ""
"" " III"

Summary: Either the left side cannot be quoted, or if there are quotes around it must be an odd number