Using Return Codes in Font

Drawer QR Code ISO/IEC18004 in Font Using Return Codes

The log file provides a wealth of information about the load, but Oracle also allows you to trap the exit code after each load run. This enables you to check the results of the load when you run it

through a cron job or a shell script. For a Windows server, you may use the at command to schedule the load job. Here are the key exit codes for the UNIX/Linux operating systems: EX_SUCC 0 indicates that all the rows were loaded successfully. EX_FAIL 1 indicates that there were command-line or syntax errors. EX_WARN 2 indicates that some or all rows were rejected. EX_FTL 3 indicates operating system errors.

So far, you have looked at the SQL*Loader utility from the point of view of a conventional load. As you recall, the conventional loading method uses SQL INSERT statements to insert the data into the tables one bind array size at a time. The direct-path loading option doesn t use the SQL INSERT statement to put data into the tables; rather, it formats Oracle data blocks and writes them directly to the database files. This direct-write process eliminates much of the overhead involved in executing SQL statements to load tables. Since the direct-path loading method doesn t contend for database resources, it will load data much faster than a conventional data load. For larger data loads, the direct-path loading method works best, and it may be the only viable method of loading data into tables for the simple reason that a conventional load may require more time than is available. Besides the obvious advantages of a shorter load time, direct loading also helps you rebuild indexes and presort table data. Using the direct-path loading method as opposed to the conventional loading method has the following advantages: The load is much faster than in the conventional loading method because you aren t using SQL INSERT statements for the load. The direct load uses multiblock asynchronous I/O for database writes, so the writing is fast. You have the option of presorting data using efficient sorting routines with the direct load. By setting the UNRECOVERABLE=Y parameter, you can avoid the writing of any redo during a direct load. By using temporary storage, you can build indexes more efficiently during a direct load than when you re using the conventional load method.

A conventional load will always generate redo entries, whereas the direct-path loading method will generate redo only under specific conditions. A direct load also won t fire any insert triggers, unlike the conventional load, which fires the triggers during the load. Users can t make any changes when a table is being loaded using a direct load, unlike in a conventional load.

However, direct-path loads have some serious limitations. You can t use this method under the following conditions: You re using clustered tables. You re loading parent and child tables together. You re loading VARRAY or BFILE columns. You re loading across heterogeneous platforms using Oracle Net. You want to apply SQL functions during the load.

In a direct load, you can t use any SQL functions. If you need to perform a large data load and also transform the data during the load, you have a problem. The conventional data load will let you use SQL functions to transform data, but the method is very slow compared to the direct load. Thus, for large data loads, you may want to consider using one of the newer load/transform techniques, such as external tables or table functions, which you ll learn about later in this chapter.

Several SQL*Loader options are intended especially for use with the direct load option or are more significant for direct loads than conventional loads. The following options are relevant to the directpath loading method: DIRECT: The DIRECT clause must be set to true in order for you to use the direct-path loading method (DIRECT=true). DATA_CACHE: The DATA_CACHE parameter comes in handy if you re loading the same data or timestamp values several times during a direct load. SQL*Loader has to convert the date and timestamp data each time it encounters them. If you have duplicate data and timestamp values in your data, you can reduce unnecessary data conversions, and thus processing time, by specifying the DATA_CACHE parameter. By default, the DATA_CACHE parameter is enabled for 1,000 values. If you don t have duplicate date and timestamp values in your data, or if there are few such duplicates, you can disable the DATA_CACHE parameter by setting it to zero (DATA_CACHE=0). ROWS: The ROWS parameter is crucial because you can use it to specify how many rows SQL*Loader will read from the input data file before saving the insertions to the tables. You use the ROWS parameter to set the ceiling on the amount of data lost if the instance fails during a long SQL*Loader run. When SQL*Loader reads the number of rows specified in the ROWS parameter, it will stop loading data until all of the data buffers are successfully written to the data files. This process is called a data save. Oracle recommends that you set the ROWS parameter such that data is saved to the table every 15 minutes. For example, if SQL*Loader can load about 10,000 rows per minute, setting ROWS=150000 saves the data every 15 minutes. UNRECOVERABLE: If you want to minimize the use of the redo log, you can do so by using the UNRECOVERABLE parameter during a direct load (UNRECOVERABLE=true). SKIP_INDEX_MAINTENANCE: The SKIP_INDEX_MAINTENANCE parameter, when turned on (SKIP_ INDEX_MAINTENANCE=true), instructs SQL*Loader not to bother maintaining the indexes during the load. The default for SKIP_INDEX_MAINTENANCE is false. SKIP_UNUSABLE_INDEXES: Setting a value of true for the SKIP_UNUSABLE_INDEXES parameter will ensure that SQL*Loader will load tables with indexes in an unusable state. These indexes won t be maintained by SQL*Loader, however. The default for this parameter is based on the setting for the SKIP_UNUSABLE_INDEXES initialization parameter, whose default value is true. SORTED_INDEXES: The SORTED_INDEXES parameter signals SQL*Loader that data is sorted on a specified index, which helps improve load performance. COLUMNARRAYROWS: This parameter determines the number of rows loaded before the building of the stream buffer. For example, COLUMNARRAYROWS=100000 loads 100,000 rows first. The size of the direct-path column array is thus determined by this parameter. The default value for this parameter on my UNIX server is 5,000.