Loading data with the SQL*Loader in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to load data using the SQL* Loader.

1. Loading methods

SQL*Loader offers two loading methods.

• Conventional-path load
• Direct-path load

Conventional-path load

A conventional-path load involves generating an array of rows on the basis of input data and loading it into the table.

SQL*Loader parses records on the basis of the field specifications in the control file.

It uses INSERT statements to load the rows into the table specified in the control file. This table can be either clustered or unclustered.

The conventional-path load type is best suited to situations in which you need to add a small amount of data to large tables.

The conventional-path load type sends records that do not match the field specifications to the bad file.

And it sends records that do not match the control file's selection criteria to the discard file.

Redo data is stored according to the specification of the relevant table's NOLOGGING attribute.

You make the changes associated with a conventional-path load permanent using a COMMIT statement.

A single load can involve multiple transactions.

All constraints are enforced during a conventional-path load.

And other users can make changes to a table into which you are loading data using a conventional-path load.

Direct-path load

A direct-path load involves generating blocks of data on the basis of input data according to the field specifications in the control file. These blocks are saved directly into the relevant table's extents in the database.

The table's extents are above the highest point in the table to which data has already been written - this is known as the high-water mark.

The blocks are written into the table one by one.

The SGA is bypassed during this type of load, but can be used for extent management and for adjustment of the high-water mark.

INSERT triggers are disabled at the start of the load, and are re-enabled when the run ends. However, they remain disabled when a referenced object can't be found.

A direct-path load can load data into an unclustered table, but not into a clustered table.

The only constraints enforced by a direct-path load are

• PRIMARY KEY
• UNIQUE
• NOT NULL

Redo entries are not generated when

• the database is in NOARCHIVELOG mode
• the database is in ARCHIVELOG mode, but logging is disabled

You can disable logging by setting the NOLOGGING attribute for the table or by using the UNRECOVERABLE clause in the control file.

Unlike conventional-path data loads, which use COMMIT statements to make changes permanent, direct-path loads use data saves.

Only full database blocks are written to the database.

After a data save, the high-water mark is moved, but indices are not updated and internal resources are not released. The data save does not end the transaction.

Other transactions can't make changes to the tables being loaded while a direct-path load is in progress.

The only exception to this rule is when several parallel direct-load sessions are used concurrently.

Question

Match each characteristic to the correct load type.

Options:

1. Can load data into an unclustered table, but not into a clustered table
2. Can load clustered tables
3. Uses a COMMIT statement to make changes associated with the load permanent
4. Uses data saves
5. Enforces all constraints

Targets:

1. Conventional-path load
   
2. Direct-path load
   

Answer

Conventional-path loads enforce all constraints, and can load clustered tables. Changes associated with the load are made permanent using a COMMIT statement. Direct-path loads can load data into an unclustered table, but not into a clustered table. They use data saves.

2. Loading data with SQL*Loader

Suppose that you wish to load data from a nonOracle database into the ORCL1 clustered database.

To do this, you need to access the SQL*Loader by opening the Load Data from File wizard.

You begin by logging into Enterprise Manager as an administrator.

In Enterprise Manager, you open the Maintenance tabbed page. This is where you carry out the loading process.

You now need to access the SQL*Loader.

In the Utilities section, you click Load Data from File.

The Load Data: Control File page displays. This is the first page of the Load Data from File wizard.

The indicator at the top of the page indicates the current stage of the loading process. There are six stages:

• Control File
• Data File
• Load Method
• Options
• Schedule
• Review

The indicator shows that the current stage of the loading process is Control File.

At any stage of the load process, you can skip to the final stage by clicking the Finish button.

By doing this, you implicitly accept the default settings associated with the stages you have skipped.

In this example, you want to work through each stage individually.

You specify the full path to the SQL*Loader control file you want to use. In this example, the control file is called LOAD1.CTL.

And you accept the default user credentials - the username and password - that enable you to use this file.

Now you want to proceed to the next stage of the process.

To proceed to the next stage of the process, you click Next.

The Load Data: Data File page displays.

In this example, the data file is specified in the control file, so you accept the default option.

You click Next to proceed to the next stage.

On the Load Data: Load Method page, you are prompted to specify the load type you require.

In this case you are loading data into a clustered database, so you ensure that Conventional Path is selected. This is the default setting.

You click Next to proceed.

The Load Data: Options page displays.

You want to specify that a bad file should be generated for the load job, so you scroll down to the relevant section of the page.

You select Generate bad file where records which contain errors are stored.

You now need to specify the name and path of the bad file.

You enter the relevant details in the Bad File field.

And you click Next to proceed.

The Load Data: Schedule page displays. You want the load to take place immediately.

To specify this, you ensure that the Immediately option is selected, and then you click Next.

The Load Data: Review page enables you to review the load settings you have specified.

You are happy with the settings you have specified.

You click Submit Job to begin the load.

The Status page displays the Load Data Submit Successful message.

Question

Arrange the six stages in the SQL*Loader process into the correct order:

Options

Option	Description
A	Specify a control file
B	Specify a data file
C	Select a load method
D	Specify whether to use a bad file
E	Review load settings
F	Schedule the load

Answer

Correct ranking

Option	Description
A	Specify a control file The Control File stage enables you to specify the name and path to the control file, as well as the credentials required to use it.
B	Specify a data file The Data File stage enables you to specify where the input data will come from.
C	Select a load method The Load stage enables you to specify whether you want to run a conventional-path load or a direct-path load.
D	Specify whether to use a bad file The Options stage enables you to specify information such as whether you want to use a bad file, and what the file should be called.
F	Schedule the load The Schedule stage enables you to specify information such as when the load job should start.
E	Review load settings The Review stage provides a summary of the settings you have specified using the wizard.

Question

You have accessed the Load Data from File wizard's Load Data: Control File page. The control file and credentials you require are already specified.

Which of these statements best describes how you load the ORCL1 database with data in the LOAD1.ctl data file, specifying that a direct-path load should be carried out, accept the remaining default settings and skip to the final stage of the wizard, and then submit the job?

Options:

1. Click Next, select Direct Path (faster), and then click Finish.
2. Click Next, click Next, select Direct Path (faster), click Finish, and then click Submit Job.
3. Click Next, select Direct Path (faster), click View Job, and then click Finish.

Answer

You click Next, click Next again, select Direct Path (faster), click Finish, and then click Submit Job.

Summary

SQL*Loader offers two load methods: conventional-path load and direct-path load. A conventional-path load involves an array of rows being added to the relevant table using INSERT triggers. A direct-path load involves saving datablocks directly in the table. Each load type has advantages and disadvantages.

You use Enterprise Manager's Load Data from File wizard to carry out a SQL*Loader load. This involves six stages: Control File, Data File, Load Method, Options, Schedule, and Review.