Detailed Syntax of Oracle SQL Loader (from the Internet)

SQL * LOADER is a data loading tool for ORACLE. It is usually used to migrate operating system files to ORACLE databases. SQL * LOADER is large data Repository selection method, because it provides the fastest way (DIRECT, PARALLEL ). Now, let's leave aside its theory and use instances to help you quickly master the use of SQL * LOADER. First, let's get to know SQL * LOADER. In NT, the SQL * LOADER command is SQLLDR, and in UNIX it is generally sqlldr/sqlload. For example, run: d: \ oracle> sqlldr. SQL * Loader: Release 8.1.6.0.0-Production on Tuesday January 8 11:06:42 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Usage: SQLLOAD keyword = value [, keyword = value,...] Valid keywords: Userid -- ORACLE username/password Control -- Control file name Log -- Log file name Bad -- Bad file name Data -- Data file name Discard -- Discard file name Discardmax -- Number of discards to allow (all default values) Skip -- Number of logical records to skip (0 by default) Load -- Number of logical records to load (all default values) Errors -- Number of errors to allow (50 by default) Rows -- Number of rows in conventional path bind array or between direct path data saves (Default: general path 64, all direct paths) Bindsize -- Size of conventional path bind array in bytes (65536 by default) 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 from Skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (FALSE by default) Skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (FALSE by default) Commit_discontinued -- commit loaded rows when load is discontinued (default value: FALSE) Readsize -- Size of Read buffer (1048576 by default) Please note: the command line parameters can be specified by the location or keyword. . The former example is 'sqlload scott/tiger foo'; the latter example is 'sqlload control = foo userid = scott/tiger '. the specified location parameter must be earlier than but cannot be later than the parameter specified by the keyword. For example, 'Sqlload SCott/tiger control = foo logfile = log', but 'disallow Sqlload scott/tiger control = foo log', even if the 'log' parameter is allowed to be in the correct position. D: \ oracle> We can see some basic help information, here, I use the Chinese WIN2000 ADV SERVER. We know that SQL * LOADER can only import plain text, so we will start to explain its usage with instances. Once the data source result.csv exists, it will be imported to the FANCY user in ORACLE. Result.csv content: 1. Default Web site, 192.168.2.254: 80:, RUNNING 2, other, 192.168.2.254: 80: test.com, STOPPED 3, third, 192.168.2.254: 81: thirdabc.com, RUNNING We can see that the four columns are separated by commas as variable-length strings. 2. Create the control file result. ctl. Result. ctl content: Load data Infile 'result.csv' Into table resultxt (Resultid char terminated ',', Website char terminated ',', Ipport char terminated ',', Status char terminated by whitespace) Note: Infile index data source file here we omit the default discardfile result. dsc badfile result. bad Into table resultxt is INSERT by default, or into table resultxt APPEND is the APPEND method, or REPLACE Terminated by ',' is separated by commas Terminated by whitespace is separated by Spaces 3. Execute loading: D: \> sqlldr userid = fancy/testpass @ tnsname (connection name in tnsname. ora) control = result. ctl log = resulthis. out SQL * Loader: Release 8.1.6.0.0-Production on Tuesday January 8 10:25:42 2002 (C) Copyright 1999 Oracle Corporation. All rights reserved. SQL * Loader-941: An error occurred while describing the RESULTXT table. ORA-04043: the object RESULTXT does not exist An error is prompted because the database does not have the corresponding table. 4. Create a table in the database Create table resultxt (Resultid varchar2 (500 ), Website varchar2 (500 ), Ipport varchar2 (500 ), Status varchar2 (500 )) / 5. Reload D: \> sqlldr userid = fancy/k1i7l6l8 control = result. ctl log = resulthis. out SQL * Loader: Release 8.1.6.0.0-Production on Tuesday January 8 10:31:57 2002 (C) Copyright 1999 Oracle Corporation. All rights reserved. The submission point is reached, and the logical record count is 2 Reaching the submission point, the logical record count is 3 Successful! We can analyze the process through the log file: resulthis. out content is as follows: SQL * Loader: Release 8.1.6.0.0-Production on Tuesday January 8 10:31:57 2002 (C) Copyright 1999 Oracle Corporation. All rights reserved. Control File: result. ctl Data File: result.csv Error file: result. bad Obsolete file: Not specified : (All records can be discarded) Quantity: ALL Skipped count: 0 Allowed error: 50 Bound array: 64 rows, up to 65536 bytes Continue: Unspecified Path used: General Table RESULTXT Loaded from each logical record The INSERT option takes effect for this table. Column name location length abort packaging Data Type -------------------------------------------------------------------------- Resultid first *, CHARACTER Website next *, CHARACTER Ipport next *, CHARACTER Status next * WHT CHARACTER Table RESULTXT: 3 rows 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 for the combined array: 65016 bytes (63 rows) Memory space allocation except bound array: 0 bytes Total number of skipped logical records: 0 Total number of logical records read: 3 Total number of rejected logical records: 0 Total number of discarded logical records: 0 Starting from 10:31:57, January 1, 2002, Tuesday The operation ended at 10:32:00, January 1, January 08, 2002. Elapsed time: 00: 00: 02.70 CPU time: 00: 00: 00.10 (optional Vi. concurrent operations Sqlldr userid =/control = result1.ctl direct = true parallel = true Sqlldr userid =/control = result2.ctl direct = true parallel = true Sqlldr userid =/control = result2.ctl direct = true parallel = true When loading a large amount of data (about 10 GB), it is best to suppress log generation: SQL> ALTER TABLE RESULTXT nologging; In this way, no redo log is generated, which can improve the efficiency. Then add a line: unrecoverable to load data in the CONTROL file. This option must be applied together with DIRECT. During concurrent operations, ORACLE claims to be able to process GB of data per hour! In fact, it may be good to reach 1-10 Gb. At first, a file with the same structure can be used, but only a small amount of data is available. After Successful loading, a large amount of data is started, which can avoid time waste. SQLLDR Problems Control File: input. ctl. The content is as follows: Load data -- 1. Control File ID Infile 'test.txt '-the name of the data file to be input is test.txt Append into table test -- 3. append records to table test Fields terminated by X '09' -- 4. The field is terminated on X '09' and is a TAB) (Id, username, password, sj) ----- defines the column Sequence Append is the data loading method, and there are other options: A. insert, which is the default mode. The table is required to be empty when data loading starts. B. append: Add a new record to the table C. replace: delete old records and replace them with newly loaded records. D. truncate, same as above