Page 329
In this chapter
Page 330
Databases today are ever increasing in complexity and size. Gigabyte-sized databases are common and data warehouses are often reaching the terabyte-sized range. With the growth of these databases, the need to populate them with external data quickly and efficiently is of paramount importance. To handle this challenge, Oracle provides a tool called SQL*Loader to load data from external data files into an Oracle database.
SQL*Loader has many functions that include the following capabilities:
SQL*Loader can be invoked by typing in sqlload, sqlldr, or sqlldr80 at the command line. The exact command may differ, depending on your operating system. Refer to your Oracle operating system-specific manual for the exact syntax. Please note that all listings and server responses in this chapter may differ with your results based on the operating system that you are using. The sqlldr command accepts numerous command-line parameters. Invoking SQL*Loader without any parameters displays help information on all the valid parameters (see Listing 14.1).
Listing 14.1SQL*Loader Help Information
Invoking SQL*Loader without parameters: $ sqlldr The server responds with help information because SQL*Loader was invoked without parameters: SQL*Loader: Release 8.0.3.0.0 - Production on Mon Nov 17 9:38:19 1997 (c) Copyright 1997 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 (Default all)
Page 331
skip -- Number of logical records to skip (Default 0) load -- Number of logical records to load (Default all) errors -- Number of errors to allow (Default 50) rows -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- Size of conventional path bind array in bytes (Default 65536) 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 (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) PLEASE NOTE: Command-line parameters may be specified either by position or by keywords. An example of the former case is `sqlload scott/tiger foo'; an example of the latter is `sqlload control=foo userid=scott/tiger'. One may specify parameters by position before but not after parameters specified by keywords. For example, `sqlload scott/tiger control=foo logfile=log' is allowed, but `sqlload scott/tiger control=foo log' is not, even though the position of the parameter `log' is correct.
SQL*Loader is an Oracle utility that loads data into the database from external data files. Figure 14.1 shows the different components of SQL*Loader.
The control file is the nerve center of SQL*Loader. This is the file that controls how data in the external data file is to be mapped into Oracle tables and columns. It should be noted that SQL*Loader input datatypes are totally independent from the database column datatypes into which they are being loaded. Implicit datatype conversions will be done as necessary and errors will be trapped if the conversion fails. The language used in the control file is the SQL*Loader Data Definition Language (DDL). The control file consists of multiple sections with many parameters, too many to cover in depth for the scope of this chapter. Refer to the Oracle Server Utilities manual for full documentation on all valid syntax and parameters of the control file. Basic syntax is covered in a later section when examples are given to demonstrate the different uses of SQL*Loader.
Page 332
FIG. 14.1
SQL*Loader
Components.
SQL*Loader can accept input data files in many different formats. Files can be stored on disk, tape, or the records themselves can be embedded into the control file. Record formats can be of fixed or variable lengths. Fixed-length records are records in which every record is the same fixed length and the data fields in each record have the same fixed length, datatype, and position. For example, the PART_NBR data item in a record would always occupy columns 10 to 19, regardless of the actual length of the data. If the part number were 12345, the remaining space in columns 15 to 19 would be blank. With variable-length records, the data item would only take up as much space as necessary on each record.
In the PART_NBR example, the data item would need only five bytes with no trailing blanks. Each record in a variable length formatted file may have different space usage for PART_NBR based on its actual length. It is important to note that even though variable-length records may use less space in the data file, data items on each record have to have a delimiter to separate the data items.
Oracle Tables and IndexesSQL*Loader can load multiple tables and indexes in an Oracle database in the same loader session. SQL*Loader's behavior in inserting the data and building the indexes will be discussed later in the section covering conventional and direct path loading.