Continuing a direct path load

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 06 Jun 2000 18:38:33 -0400
Message-Id: <10520.107979@fatcity.com>

I've been doing some research, and it doesn't appear possible to reliably determine the number of records to skip when continuing a direct path load, at least not without going in and looking at the raw data. Here's some output from a direct path load involving 3 tables that failed because one of those tables reached its maximum number of extents:

SQL*Loader: Release 8.1.5.0.0 - Production on Tue Jun 6 17:44:16 2000

Save data point reached - logical record count 1.
Save data point reached - logical record count 1.
Save data point reached - logical record count 1.
Save data point reached - logical record count 1.
Save data point reached - logical record count 2.
Save data point reached - logical record count 2.
Save data point reached - logical record count 2.
Save data point reached - logical record count 3.
Save data point reached - logical record count 4.

ORA-02356: The database is out of space. The load cannot continue
ORA-01631: max # extents (1) reached in table GNIS.CHU_CHURCH Save data point reached - logical record count 4. ORA-02356: The database is out of space. The load cannot continue
ORA-01631: max # extents (1) reached in table GNIS.CHU_CHURCH Save data point reached - logical record count 4. ORA-02356: The database is out of space. The load cannot continue
ORA-01631: max # extents (1) reached in table GNIS.CHU_CHURCH Save data point reached - logical record count 5. ORA-02356: The database is out of space. The load cannot continue
ORA-01631: max # extents (1) reached in table GNIS.CHU_CHURCH Save data point reached - logical record count 5. ORA-02356: The database is out of space. The load cannot continue
ORA-01631: max # extents (1) reached in table GNIS.CHU_CHURCH Save data point reached - logical record count 6.

Load completed - logical record count 1484.

The log file from this aborted load looks like this:

Table AIR_AIRPORT:
  1484 Rows successfully read.
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1478 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null.

Table CHU_CHURCH:
  1299 Rows successfully read.
  181 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1118 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null.

Table HOS_HOSPITAL:
  1471 Rows successfully read.
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1467 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null.

To continue this load, you would need to skip 1012 rows for the the CHU_CHURCH table, 1247 for the AIR_AIRPORT table, and 1192 for the HOS_HOSPITAL table. I understand how to do that using CONTINUE_LOAD, but I'm surprised that SQL*Loader doesn't provide you with the needed information. Am I missing something fundamental, or is this just the way that it is?

Jonathan

Received on Tue Jun 06 2000 - 17:38:33 CDT