Oracle SQL loader uses Quick Start

Oracle SQL loader uses quick Oracle SQL LOADER, which is an Oracle Data Loading tool used to load data from fixed text files to Oracle databases. It provides the fastest Loading Method (direct/parallel) and is a perfect tool for fast loading of big data. It is widely used in data warehouse applications. Let's take a look at its quick usage and quickly grasp it. Www.2cto.com 1. determine the content format of the loaded file and the character set encoding of the file. The imported text files are suffixed with txt or csv. The encoding methods are generally gb2312 and utf8. For example, the csv file content format is as follows: Csv code "admin", "55de42146747ea457c2bab5064b7019c", "webmastor@yourdomain.com", "d0d8e1" "huangomeng", "Hangzhou", "huangomeng@163.com ", "145288" "Le you", "friends", "zhnaeu@yahoo.cn", "a2fb84" "belon", "894a14224560a9792f0a21673642e2cc", "50187908@163.com", "583815" each field is enclosed in double quotes, fields are separated by commas. Basically, the csv format is like this. In addition, pay attention to the differences between different encoding character sets, such as gb2312 and utf8. It is required when writing sqlldr control files. 2. Write the correct data load control file and LOAD the text file of the character set ZHS16GBK. The content of the control file bb_11g.ctl is as follows: \ comment 'discardfile 'd: \ uc_members_20121130.dsc 'truncate into table T_USER_INFO (username char terminated by', 'optionally enclosed by '"', passwd char terminated ', 'optionally enclosed by '"', email char terminated by', 'optionally E Nclosed by '"', SALT char terminated by whitespace optionally enclosed by '"') www.2cto.com the character set name is the same as the Character Set Name of the database. If the character set does not match, after loading the data into the database, you will find that Chinese characters or other files are garbled. If the database character set is ALT32UTF8, you need to change "CHARACTERSET ZHS16GBK" to "CHARACTERSET UTF8 ". Description of field delimiters: terminated by x' 09 ', which indicates that the fields are separated by x' 09' -- tabs (TAB), and commas (,) are used to separate terminated by whitespace, separated by white space at the end. 3. Use the sqlldr program to import text files to the database based on the control file and execute sqlldr in windows cmd to quickly load data. Cmd code sqlldr scott/tiger @ elvdb_11g control = d: \ bb_11g.ctl log = d: \ dd_11g.log after execution, remember to check the log file. The log file displays the loaded details. Log Code SQL * Loader: Release 10.2.0.1.0-Production on Friday November 30 10:17:44 2012 Copyright (c) 1982,200 5, Oracle. all rights reserved. control File: D:/bb_11g.ctl specifies the character set ZHS16GBK for all input. Data File: D: \ uc_members_20121130.csv error file: D: \ uc_members_20121130.bad obsolete file: D: \ uc_members_20121130.dsc (ALL records can be discarded) number of records to be loaded: the number of ALL records: 0 allowed error: 50 bound array: 64 rows, up to 256000 bytes continue: unspecified path: general table T_USER_INFO, the inserted option from each logical record has been loaded. This table TRUNCATE takes effect. Column name Position length abort packaging data type -------------------------- ---------- ----- ---- ----------------- username first *, O (") character passwd next *, O (") CHARACTER EMAIL N EXT *, O (") character salt next * wht o (") CHARACTER table T_USER_INFO: Row 1132907 is loaded successfully. Zero rows are not loaded due to data errors. Because all the WHEN clauses fail, the 0 rows are not loaded. Because all fields are empty, 0 rows are not loaded. Space allocated to the bound array: 66048 bytes (64 rows) read buffer Bytes: 1048576 skipped logical records Total: 0 read logical records Total: 1132907 reject logical records total: 0 total number of discarded logical records: 0 from Friday November 30 10:17:44 2012 start running on Friday November 30 10:18:23 end running time: 00: 00: 2012 CPU time: 00: 00: 06.16 from the log file, we can see that it takes 39 seconds to load 1.1 million records. If an error or obsolete record exists, you can view it in the corresponding file. Iv. NOTE 1: The imported text file is sometimes very large and it may be difficult to open it using the text editing tool. Try vi. 2. It is recommended that the imported target table have no primary key, no index, and no constraints. Use nologging to minimize log generation. 3. It is recommended that all formats of imported text files be strings or numbers. If it is time, it must be consistent with NSL_DATE_FORMAT. 4. For the LOB field, sqlldr cannot help.