sqlldr example

Tags: details rail har file error changing build break garbled problemsolving the problem of importing garbled sqlldr Process: 1, local build control file Load data infile ' d:\TMP_KAITOUSHUJU.csv ' into table Tmp_kaitoushuju fields terminated by ', ' (TR Ade_co,full_name,customs_code,co_class,valid_date,rg_date,addr_co,busi_type,contac_co,tel_co,law_man,law_man_ Tel,co_type,break_law_time1,break_law_time2,inspect_time,cop_modify_date) 2, under Wind

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

Objective Recently do projects need to import a batch of 3000多万条 POI data to Oracle database, simple insert import speed is too slow, use SQLLDR batch import 3000多万条 data spent 20 minutes, speed can also, now share to everyone, the specific methods are as follows: 1. New import Control file Input.ctl, file contents as follows: Load data Characterset UTF8 Infile ' H:\POI\baidu.txt ' Append into table Tbl_poi_baidu fields Terminated by "," opt

Command:Sqlldr Userid=[username]/[password]@[database] Control=[filepath]Username User NamePassword PasswordDatabase DB InstanceFilePath the physical path of the CTL file-----------------------------------------------------------------------File:Load datainfile *append into table [Tablename]fields terminated by "," trailing nullcols ([ID] sequence (), [col1],TableName Table nameID PRIMARY Keycol1,2,3,4,5,6 Field NameOracle uses SQLLDR (with sequence)

1. Create a control file Options (direct = true)Load dataInfile '/var/log/xferlog. tmplog'Into table ta_ftp_logAppendFields terminated by '|'Trailing nullcols(LOGID integer external,Invoketime DATE 'yyyymmdd hh24: mi: ss ',Interfacename char,Interfacetype char,Invokesysname char,Transfertime integer external,Transfertyep char,Specialflag char,Transferto char,Model char,Username char,Status char,Filename char) 2. Use the sqlldr command

: Specify the data loading method, there are four kinds of values: [(1) Insert, the default way, at the beginning of the data load requires the table is empty, (2) Append, delete the old record (with the DELETE from table statement), replace the new loaded record; (3) Replace , delete the old record, replace it with the new loaded record, (4) Truncate, delete the old record (with the TRUNCATE TABLE statement), replace the new loaded record]; Here I specify the way for appendField terminated by:

thing to note about this place isExport loadfilename= $data _dir "/" $fileThis will Loadfilename (import Oracle text, absolute path) This variable export to the Linux environment, so that the Sqlload control file can read this variable and import2.sqlldr Control text Dayflow.log.ctlLoad Data CHARACTERSET Al32utf8 infile ' $LoadFileName ' APPEND into table User_1.bil_flux_high_cur Fields terminated by ' | ' Trailing Nullcols (ACCS_NBR "trim (: ACCS_NB

Oracle uses sqlldr (with sequence) to import data Command: Sqlldr userid = [USERNAME]/[PASSWORD] @ [DATABASE] control = [FILEPATH] Username Password Database instance Filepath: physical path of the ctl File ----------------------------------------------------------------------- File: Load datainfile * append into table [TABLENAME] fields terminated by "," trailing nullcols ([ID] sequence (1, 1)

Test procedure: 1. Create a table: Create table spread_detail ( Id number, Datecreated DATE, Partnername VARCHAR2 (200 BYTE ), Adform VARCHAR2 (100 BYTE ), Ipaddress VARCHAR2 (20 BYTE ), Isavail NUMBER (1 ), Regflag NUMBER (1 ), Memo1 VARCHAR2 (100 BYTE) ); Create unique index idx_spread_detail ON spread_detail (ID ); 2. Create a sequenceCreate sequence seq_spread_detail_id Start with 0 Max value 999999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER; 3. Write the automatically executed shell script file

Oracle uses sqlldr (with sequence) to import data, sqlldrsequence Command: Sqlldr userid = [USERNAME]/[PASSWORD] @ [DATABASE] control = [FILEPATH] Username Password Database instance Filepath: physical path of the ctl File ----------------------------------------------------------------------- File: Load datainfile * append into table [TABLENAME] fields terminated by "," trailing nullcols

Tags: scott suffix into style match explain control load numberRequirements: Data migration, import the contents of TXT file into the table of Oracle database, the data format of the text file is as follows (data is separated by space) 1. Create a table that matches the text data format (created here under the Scott user) Create Table Li_an ( varchar2(+), varchar2(), Number , varchar2 (+), varchar2 (+) ); 2. Create a file with

/nullMkdir-p $cmdLogDir 2>/dev/nullFor datafile in ' LS ${datadir}/*.del 'Dologdatesuffix= ' Date ' +%y%m%d%h%m%s "'Sqlldr $dbConnStr \Silent=all errors=10000 \Data= $dataFile \log= $impLogDir/' basename ${datafile} '. $logDateSuffix. IMP \bad= $badLogDir/' basename ${datafile} '. $logDateSuffix. Bad \control= $controlFile/' basename ${datafile} '. CTL \>/dev/null \2>> $cmdLogDir/' basename ${datafile} '. ${logdatesuffix}.logDoneRun Script $./impdata.

FALSENLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 10.2.0.4.020 rows selected.Confirm that the character set selected when the database was created is a UTF-8. Execute SQL: select userenv ('language ') from dual; get oracle Server character set XSQL> select userenv ('language') from dual;USERENV ('language ')----------------------------------------------------SIMPLIFIED CHINESE_CHINA.UTF82. Check the settings of NLS_LANG on the client that executes sqlldr.Oracle client execution[Oracle @ lo

1. LOAD DATAINFILE *Into table testFields terminated by x '9'TRAILING NULLCOLS(C2 "upper (: c2 )",C3 date "yyyymmdd ",C1 "SEQ_test_c1.nextval")BEGINDATAAAAAAAAAAA 20051201Bbbbbbbbbbbb 20050112CREATE TABLE TEST ( C1 VARCHAR2 (10 BYTE ), C2 VARCHAR2 (20 BYTE ), C3 DATE ); Create sequence VV_USER.SEQ_TEST_C1 Start with 0 Max value 9999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER; 2. Load dataInfile *Into Table TestFields terminated by X '9'Trailing nullcols(C2 "upper (: C2 )",C3 "to_date (: C3, 'yyyy-m

is retained, the inserted data is binary name "upper (: Name)", converting the inserted value to uppercase con ": id| |:name", The value of the Con column in the table is the combined value of the ID and name dt date "YYYY-MM-DD" to insert datetime data) in the Append location can also be used for one of the values in the following list: Insert inserts a value into the table, but requires that the table be empty replace Delete the data in the table, and then insert the new val

' spool/datafile/zmcf/zmcf_csv/' | | UPPER (table_name) | | '. CSV ' Txt_name,table_name, 0 CID from User_tables a where a.table_name in (select table_name from tab_column_union)UNION ALLSelect t.tab_column_source,t.table_name,0.1 from Tab_column_union tUNION ALLSelect ' Spool off ', table_name,0.7 CID from User_tables a where a.table_name in (select table_name from tab_column_union)) AA order by Table_name,cid; Spool off Connect to the Source library, Set Lines 1000Set pages 0Set echo offSet F

Knowledge Point1. automatically generate ID2. fixed-length Data Processing3. Use custom functions. Unrecoverable Load DataInfile ' Tmp.txt ' Badfile ' TMP. Bad ' Truncate Into Table Sss(ID sequence ( Max , 1 ),Caller ID position ( 01 : 22 ) "Decode (: caller number, ' 00c ' , '' ,: Caller number )",The length of the caller's number "decode (: caller's number, ' 00c ' , 0 , Length (: caller number ))",Called Number positio

('language ')----------------------------------------------------SIMPLIFIED CHINESE_CHINA.UTF82. Check the settings of NLS_LANG on the client that executes sqlldr.Oracle client execution[Oracle @ localhost hx] $ echo $ NLS_LANGAMERICAN_AMERICA.UTF83. Try to make the character set settings checked in the preceding three steps consistent. Start to import text through sqlldr and check the results.There are three possible causes:1. the character set spec

--Create TableCreate TableDEPT2 (DEPTNO Number(2) not NULL, DnameVARCHAR2( -), LOCVARCHAR2( -));Alter TableDEPT2Add constraintDept_pkPrimary Key(DEPTNO); ------Demo.ctlLOADDatainfile*---* indicates that the data is in the control file into TABLEDEPT2INSERT---The default load modeFields TERMINATED by ','---separators(DEPTNO, dname, LOC)---The default type is char (255) BegindataTen, Sales,virginia -, Accounting,virginia -, Consulting,virginia +, Finance,virginiasqlldr userid=Scott/Dcjet Control=/

Label:1. Create a new file Test.ctl with the following contentLoadDatainfile'Vodall.csv'Append into TableZjsm.vod_record_allfields terminated by '\ t'trailing Nullcols (virtual_column FILLER, Watch_time "To_date (SUBSTRB (: Watch_time,1, +),'YYYY-MM-DD HH24:MI:SS') ", device_id, program_id, Program_name, Program_type, watch_duration number,//default is character type Program_providern AME)Description InFile ' Vodall.csv ' indicates the file to be read Append into table Zjsm.vod_reco