Page 377
NOTE |
When using the parallel loader, you can specify multiple input files and tables. The FILE parameter specified in the OPTIONS clause becomes the global default for all INSERT INTO TABLE options clauses. In addition to the OPTIONS clause for the load, each INSERT INTO TABLE clause can have an OPTIONS clause, which specifies the tablespace file where the temporary segments are created. (See Listing 16.1.) If multiple input files are specified or multiple tables are loaded, you should further qualify the options in the options section for that table. |
The UNRECOVERABLE/RECOVERABLE options apply only to direct path loads; all conventional loads are by definition recoverable. When the database is in archivelog mode, if RECOVERABLE is specified, the loaded data is written to the redo logs. Specifying UNRECOVERABLE bypasses writing to the redo logs, which improves performance (by about 100 percent) but forces you to drop and re-create the loaded data if media recovery is required.
TIP |
Unless you are appending data to existing data in a table, you should specify UNRECOVERABLE for performance issues. If for some reason the instance needs recovery, recover it, and truncate the tables into which you were loading the data. Then start the SQL*Loader session again. |
The LOAD DATA clause is the main statement in the control file. Only comments, the OPTIONS clause, and the RECOVERABLE clause can precede LOAD DATA in the control file. LOAD DATA is followed by phrases and clauses that further qualify it. For the complete syntax of the control file, refer to the Oracle8 Server Utilities Guide.
The LOAD DATA clause begins with the keyword LOAD, optionally followed by the keyword DATA. Note in Listing 16.3 that the keyword CHARACTERSET followed by the character set name is required if the input data file is from a foreign character set, in this case EBCDIC. The control file can have only one LOAD DATA clause.
To specify the input file containing the data to be loaded, specify the INFILE or INDDN keyword, followed by the filename and an optional O/S-dependent file-processing specifications string. Note in Listing 16.4 that the string "FIX 28" follows the complete file pathname for a UNIX system. Listing 16.4 contains some examples of file specifications from other platforms.
Page 378
Listing 16.4. Sample file specifications.
INFILE myfile.dat INFILE `c\loader\input\march\sales.dat' INFILE `/clinical/a0004/data/clin0056.dat' "recsize 80 buffers 10" BADFILE `/clinical/a0004/logs/clin0056.bad' DISCARDFILE `/clinical/a004/logs/clin0056.dsc' DISCARDMAX 50 INFILE `clin_a4:[data]clin0056.dat' DISCARDFILE `clin_a4:[log]clin0056.dsc' DISCARDMAX 50
NOTE |
As a rule, use single quotes around any file specification that contains punctuation marks. |
Following the INFILE statement is the optional bad file specification, which begins with the keyword BADFILE or BDDN followed by the filename. If no name is specified by the bad file, the name defaults to the name of the data file followed by a .BAD extension. A bad file is created only if records were rejected because of formatting errors or the Oracle kernel returned an error while trying to insert records into the database.
Following the BADFILE statement is the optional discard file specification, which begins with the keyword DISCARDFILE or DISCARDDN and is followed by the filename. Next comes a DISCARDS or DISCARDMAX keyword with an integer specification. SQL*Loader might create a discard file for records that do not meet any of the loading criteria specified in the WHEN clauses in the control file. If no discard file is named and the DISCARDS and DISCARDMAX keywords are not specified, a discard file is not created even if records were discarded. If, however, the DISCARDS or DISCARDMAX keyword is specified on the command line or in the control file, with no discard filename specified, a default file is created with the name of the data file followed by a .DSC extension.
The DISCARDS or DISCARDMAX clause limits the number of records to be discarded for each data file. If a limit is specified, processing for the associated data file stops when the limit is reached.
Page 379
NOTE |
For both the bad file and discard files: |
The table loading method keyword specifies the default global method for loading the tables. You can use one of four methods:
INSERT is the default method and requires the table to be empty before loading the data file. SQL*Loader terminates with an error if the table is not empty.
APPEND adds new rows to the table, even if the table is empty.
REPLACE uses the SQL DELETE command to delete all rows from the table, performs a commit, and then loads the new data into the table.
TRUNCATE uses the SQL TRUNCATE command to remove the rows from the table, performs a commit, and then loads the new data into the table. All referential constraints must be disabled before the SQL*Loader session begins; otherwise , SQL*Loader terminates with an error.
WARNING |
When you use the REPLACE method, if you specified DELETE CASCADE for the table, then the cascaded deletes are also performed. Any delete triggers you defined for the table also fire as the rows are deleted. |
In addition to specifying a global table load method, you can specify a method for each table in the INTO TABLE clause.