Oracle SQL*Loader: The Definitive Guide

Chapter 6. Recovering from Failure

What do you do when you are loading data with SQL*Loader, and something goes wrong, and the load fails? That’s an important question, because sooner or later you’ll find yourself in just that situation. In some cases, you may be able to restart the load from the beginning. Otherwise, you’ll need to determine how many records were successfully processed and loaded, so that you can restart the load from that point forward.

Warning

It’s important to think through your plans for restarting a load before you actually begin a load operation. In some cases, it can be very difficult to determine the precise point from which to restart.

There are many reasons why a load might fail. Problems you might encounter include the following:

• A table or index may reach its maximum allowed number of extents.

• You may run out of space in a tablespace.

• You may suffer an instance crash during a load.

Whichever problem occurs, you obviously need to fix it before you restart the load. If you run out of space in a tablespace, for example, you’ll need to add a datafile to the tablespace, or increase the size of an existing datafile, before you try the load again.

An easy solution, when a load fails, is to simply start over and redo the entire load. However, if a lot of data is involved, starting over is not very efficient. SQL*Loader provides a mechanism to restart a load and pick up from the point where the previous attempt failed. By using this mechanism, you avoid the need ...