Oracle SQL * Loader User Guide

One: The characteristics of SQL Loader Oracle have a lot of tools that can be used for data migration, backup, and recovery. But each tool has its own characteristics. For example, EXP and IMP can be exported and exported to data in the database, which is a good database backup and recovery tool, so it is mainly used in the hot backup and recovery of the database. There is a fast speed, simple and fast, and some shortcomings, such as the export between different versions of the database, the process of importing, there will always be such a problem, this may be the compatibility of Oracle's own products. Sexual question. The SQL Loader tool does not have this problem, which can put some data stored in the text format into the Oracle database, is a very convenient and universal tool for data migration between different databases. Disadvantages are slower, and additional data such as blob is a bit troublesome. Two: SQL Loader Help

C: /> SQLLDR

SQL * Loader: Release 9.2.0.1.0 - Production on Saturday October 9 14:48:12 2004

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Usage: SqlLDR Keyword = Value [, Keyword = Value, ...]

Effective keyword:

Userid - Oracle Username / Password Control - Control File Name Log - Log File Name Bad - Bad File Name Data - Data File Name Discard - Discard File Namediscardmax - Number of Discards To Allow - Skip - - Number of Logical Records To Skip (Default 0) Load - Number of Logical Records To Load (All Default) Errors - Number of Errors To Allow (Default 50) Rows - Number of Rows in Convenctional Path Bind Array or Between Direct Path Data Saves (Default: General Path 64, All Direct Path) BINDSIZE - SIZE OF Conventional Path Bind Array In Bytes (Default 256000) Silent - Suppress Messages During Run (Header, Feedback, Errors, Discards, Partitions Direct - use direct path (default FALSE) parfile - parameter file: name of file that contains parameter specifications parallel - do parallel load (default FALSE) file - File to allocate extents fromskip_unusable_indexes - disallow / allow unusable indexes or index partitions (default False) SKIP_IN dex_maintenance - do not maintain indexes, mark affected indexes as unusable (default FALSE) readsize - Size of Read buffer (default 1048576) external_table - use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (default NOT_USED) columnarrayrows - Number of rows for direct path column array (default 5000) streamsize - Size of direct path stream buffer in bytes (default 256000) multithreading - use multithreading in direct path resumable - enable or disable resumable for current session (default FALSE) resumable_name - - TEXT STRING TO HELP IDENTIFY Resumable StatementResumable_timeout - Wait Time (In Seconds) for Resumability (Default 7200) Date_Cache - Size (in Entries) of Date Conversion Cache (default 1000)

Please Note: The command line parameter can be specified by the location or keyword. The former is the 'sqlloadscott / tiger foo'; one example of the latter case is 'sqlldr control = fooouserid = scott / tiger'. The time specified by the specified parameter must be premature than the parameter specified by the keyword. For example, 'SqlLDR Scott / Tiger Control = Foo logfile = log', but does not allow 'sqlldr scott / tiger control = foo log', even if the parameter 'log' is positioned correctly. C: />

Three: SQL Loader Examples a) SQLLoader exports Excel data to Oracle1. Creating SQL * Loader Entering the files required to enter data, Save to C: /, with Notepad Edit: Control File: Input.ctl, the content is as follows: loading Data --1, control file identifier infile 'test.txt' --2, the data file to be entered is named Test.txt append Into Table Test --3, adding recorded records in Test Test Fields Terminated by X'09 '- -4, field termination in X'09 ', is a Tab (ID, username, password, sj) ----- defining column corresponding order A, INSERT, for the default mode, starting in data loading When the table is empty B, append, add new records C, Replace, delete old records, replace the newly loaded record D, TRUNCATE, using the SQL * loader command to implement data in the DOS window C: / > SQLLDR Userid = System / Manager Control = INPUT.CTL The default log file name is: Input.log Default bad log file is: Input.bad 2. There is also a way to save the Excel file as a CSV (comma separated) (*. CSV), the control file is changed to separate the load data infile 'd: /car.csv' append infile 'd: /car_temp fields terminated by "," (Phoneno, VIP_CAR)

b) import data directly in the control file

1. Contents Contents Test.ctl - The Format for Executing this file with sql loader is: - SQLLDR Control = Be Sure to Substitution Your - Version of Sql Loader and The FileName for this file.load DataInfile * BADFILE 'C: / Documents and Settings / Jackey / Desktop /WMCOUNTRY.BAD'DISCARDFILE' C: / Documents and Settings / Jackey / Desktop /WMCOUNTRY.DSC'INSERT INTO TABLE EMCCOUNTRYFields terminated by ";" Optionally enclosed by ' "' (COUNTRYID NULLIF (COUNTRYID = "NULL"), COUNTRYCODE, COUNTRYNAME, cONTINENTID NULLIF (cONTINENTID = "NULL"), mAPID NULLIF (mAPID = "NULL"), CREATETIME DATE "MM / DD / YYYY HH24: MI: SS" NULLIF (CreateTime = "NULL"), LastModifiedTime Date "MM / DD / YYYY HH24: MI: SS" NULLIF (LastModifiedTime = "NULL")) Begindata1; "JP"; "jap"; 1; 9; "09/16 / 2004 16:31:32 "; NULL2;" CN ";" China "; 1; 10;" 09/16/2004 16:31:32 "; NULL3;" in ";" india "; 1; 11;" 09/16/2004 16:31:32 "; null4;" au ";" australia "; 6; 12;" 09/16/2004 16:31:32 "; NULL5;" CA ";" canada "; 4 ; 13; "09/16/2004 16:31:32"; null6; "US"; "United States"; 4; 14; "09/16/2004 16:31:32"; NULL7; "MX"; "MEX ICO "; 4; 15;" 09/16/2004 16:31:32 "; NULL8;" GB ";" United Kingdom "; 3; 16;" 09/16/2004 16:31:32 "; NULL9; "De"; "germany"; 3; 17; "09/16/2004 16:31:32"; null10; "fr"; "france"; 3; 18; "09/16/2004 16:31:32 "; Null11;" it ";" italy "; 3; 19;" 09/16/2004 16:31:32 "; NULL12;" ES ";" spain "; 3; 20;" 09/16/2004 16 : 31: 32 "; NULL13;" FI ";" Finland "; 3; 21;"

09/16/2004 16:31:32 "; NULL14;" SE ";" Sweden "; 3; 22;" 09/16/2004 16:31:32 "; NULL15;" IE ";" Ireland "; 3 ; 23; "09/16/2004 16:31:32"; NULL16; "NL"; "Netherlands"; 3; 24; "09/16/2004 16:31:32"; NULL17; "DK"; " "09/16/2004 16:31:32"; NULL18; "Br"; "BRAZIL"; 5; 85; "09/30/2004 11:25:43"; null19; " Kr ";" Korea, republic of "; 1; 88;" 09/30/2004 11:25:43 "; null20;" NZ ";" new Zealand "; 6; 89;" 09/30/2004 11: 25:43 "; null21;" be ";" be "; 3; 79;" 09/30/2004 11:25:43 "; NULL22;" AT ";" austria "; 3; 78;" 09/30 / 2004 11:25:43 "; NULL23;" NO ";" norway "; 3; 82;" 09/30/2004 11:25:43 "; NULL24;" Lu ";" luxembourg "; 3; 81; "09/30/2004 11:25:43"; NULL25; "PT"; "Portugal"; 3; 83; "09/30/2004 11:25:43"; NULL26; "GR"; "Greece"; 3; 80; "09/30/2004 11:25:43"; NULL27; "IL"; "Israel"; 1; 86; "09/30/2004 11:25:43"; NULL28; "CH"; "Switzerland"; 3; 84; "09/30/2004 11:25:43"; NULL29; "A1"; "Anonymous Proxy"; 0; 0; "09/30/2004 11:25:43"; NULL30 "A2"; "Satellite Provider"; 0; 0; "09/30/2004 11:25:43"; NULL31; "AD"; "Andorra"; 3; 0; "09/30/2004 11:25 : 43 "; NULL32;" AE ";" United Arab Emirates "; 1; 0;" 09/30/2004 11:25:43 "NULL33;" AF ";" afghanistan "; 1; 0;" 09/30/2004 11:25:43 "; NULL34;" AG ";" Antigua and Barbuda "; 7; 0;" 09/30 / 2004 11:25:43 "; NULL35;" Ai ";" Anguilla "; 7; 0;" 09/30/2004 11:25:43 "; NULL36;" Al ";" albania "; 3; 0;"

09/30/2004 11:25:43 "; NULL37;" AM ";" armenia "; 3; 0;" 09/30/2004 11:25:43 "; null38;" an ";" Netherlands Antilles "; 3; 0; "09/30/2004 11:25:43"; NULL39; "AO"; "Angola"; 2; 0; "09/30/2004 11:25:43"; NULL40; "AP"; "Asia / Pacific Region"; 2; 0; "09/30/2004 11:25:43"; NULL41; "AQ"; "Antarctica"; 8; 0; "09/30/2004 11:25:43" NULL42; "Ar"; "argentina"; 5; 0; "09/30/2004 11:25:43"; NULL43; "AS"; "American samoa"; 6; 0; "09/30/2004 11 : 25: 43 "; NULL44;" AW ";" aruba "; 5; 0;" 09/30/2004 11:25:43 "; NULL45;" AZ ";" azerbaijan "; 1; 0;" 09 / 30/2004 11:25:43 "; null46;" ba ";" bosnia and herzegovina "; 3; 0;" 09/30/2004 11:25:43 "; NULL47;" BB ";" Barbados "; 5 0; "09/30/2004 11:25:43"; NULL48; "BD"; "Bangladesh"; 1; 0; "09/30/2004 11:25:43"; NULL49; "BF"; Burkina Faso "; 2; 0;" 09/30/2004 11:25:43 "; NULL50;" BG ";" bulgaria "; 3; 0;" 09/30/2004 11:25:43 "; NUL51; "BH"; "bahrain"; 1; 0; "09/30/2004 11:25:43"; NULL52; "Bi"; "Burundi"; 0; 09/30/2004 11:25:43 "NULL53;" BJ ";" benin "; 2; 0;" 09/30/2004 11:25:43 "; NULL54;" BM ";" bermuda "; 4; 0; 09/30/2004 11 : 25:43 "; NULL55;" BN ";" brunei darussalam "; 1; 0;" 09/30/2004 11:25:43 "NULL56;" BO ";" BoliVia "; 5; 0;" 09/30/2004 11:25:43 "; NULL57;" BS ";" bahamas "; 7; 0;" 09/30/2004 11 : 25: 43 "; null58;" bt ";" bhutan "; 1; 0;" 09/30/2004 11:25:43 "; null59;" BV ";" bouvet island "; 5; 0;"

09/30/2004 11:25:43 "; NULL60;" BOTSWANA "; ​​2; 0;" 09/30/2004 11:25:43 "; Null61;" BELARUS "; 3 ; 0; "09/30/2004 11:25:43"; NULL2, execute the import command C: /> SQLDR Userid = system / manager control = test.ctlc) Import of complex format