Lesson 9

This module looked at a variety of new features and enhancements that make life easier for the DBA. You saw how to load data, shuffle tables around, and modify table structures without dropping the tables.
Now that you have completed this module, you should be able to:

1. List improvements to SQL*Loader
2. Load LOB data with SQL*Loader
3. Describe enhancements in table management
4. Relocate a table
5. Describe why and how to create a temporary table
6. Find and remove an unused column from a table
7. List new database limits

Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.

The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. If you do not specify a bad file and there are rejected records, then SQL*Loader automatically creates one. It will have the same name as the data file, with a.bad extension. Some of the possible reasons for rejection are discussed in the next sections.

Data file records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, then SQL*Loader rejects the record. Rejected records are placed in the bad file.

After a data file record is accepted for processing by SQL*Loader, it is sent to the Oracle database for insertion into a table as a row. If the Oracle database determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file. The row may be invalid, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.

As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file. The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, then execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.

Workspace Manager is tightly integrated with the Oracle database. Oracle Database 10g Workspace Manager enhancements included the ability to export and import version-enabled tables, to use SQL*Loader to bulk-load data into version-enabled tables, to trigger events based on workspace operations, and to define workspaces that are continually refreshed. Oracle Database 11g continues the stream of enhancements to workspaces, providing support for optimizer hints and more data maintenance operations on workspace-enabled tables.

You were introduced to the following term in this module.

1. Temporary table: a table that is created like a regular table, but whose data is temporary and private (seen only by the user that inserts the data, even if multiple users are using the same temporary table at once)

In the next module, you will review the concept of table joins and see how Oracle implements outer joins and adds new functionality with special query formats.

Click the quiz link below to take a quiz on Database elements.
Database Elements - Quiz