SQL*Loader-605: Non-data dependent ORACLE error occurred

oracleoracle-11g-r2sql-loader

As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error.

Data is (are?) in .txt file and its size is 95GB.

Here are some more details about the error:

value used for ROWS parameter changed from 64 to 19

ORA-01653: unable to extend table M.ECZ by 8192 in tablespace USERS

SQL*Loader-605: Non-data dependent ORACLE error occurred — load discontinued.

Specify SKIP=8351926 when continuing the load.

Table ECZ:
8351925 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 245100 bytes(19 rows)
Read buffer bytes: 1048576

Total logical records skipped: 1

Total logical records rejected: 0

Total logical records discarded: 0

What do you advice? Thanks.

Best Answer

You've simply run out of space in the DB you are loading the data into. Try:

SQL> select file_name, bytes/1024/1024/1024 as gb from dba_data_files where tablespace_name='USERS';

Let's say the file was `/data/users01.dbf', 32G in size. Then you might do

SQL> alter tablespace users add datafile '/data/users02.dbf' size 1g autoextend on next 1g maxsize unlimited;

That will give you up to another 32G of space. Repeat as necessary, 03, 04 etc. I don't know how much Oracle data your 95G text file will end up as, but you can see what is available with:

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as Gb from dba_free_space where tablespace_name='USERS' group by tablespace_name;
Related Question