My Oracle Support Banner

SQL*Loader Fails To Load Into Partitioned Table When Tablespaces Are Offline (Doc ID 268726.1)

Last updated on OCTOBER 01, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.1.7 to 9.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


Assume a partitioned table which has partitions on different tablespaces. Create a local index on the partitioned table.

Use SQL*Loader to load data that you know would fall into a particular partition only. The tablespaces for the table partitions and local partitioned indexes,  where the data is being loaded, are in READ WRITE mode. If the tablespaces of  other table partitions and local index partitions are offline, then SQL*Loader fails with the following error:

ORA-376: file 2 cannot be read at this time

where file 2 is not at all related to the tablespace of the partition where the data is to be loaded.

But SQL*Loader works fine in the same scenario when there is no local index on the partitioned table. This is failing only for the direct path load and not conventional load.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.