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

Question

I am getting error when I try to load Incl_c.ctl, all other control files are loading perfectly fine. Here's the error:

[navy10@sit ~]$ sqlldr navy10@studb10g Incl_C.ctl
Password:

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Mar 27 10:26:52 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-2028: load discontinued to user interrupt (Ctrl-C) [103]
SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

here's the control file for Incl_C:

LOAD DATA
INFILE 'Incl_C.dat'
REPLACE INTO TABLE Includes_C
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
(Tnum, ConfID)

Here's the .log file for Incl_C:

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Mar 27 10:26:52 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   Incl_C.ctl
Data File:      Incl_C.dat
  Bad File:     Incl_C.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table INCLUDES_C, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TNUM                                FIRST     *   ,  O(') CHARACTER
CONFID                               NEXT     *   ,  O(') CHARACTER

Record 22: Rejected - Error on table INCLUDES_C, column TNUM.
Column not found before end of logical record (use TRAILING NULLCOLS)
SQL*Loader-2028: load discontinued to user interrupt (Ctrl-C) [103]
ORA-01013: user requested cancel of current operation

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


Table INCLUDES_C:
  0 Rows successfully loaded.
  1 Row 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:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Mar 27 10:26:52 2013
Run ended on Wed Mar 27 10:27:01 2013

Elapsed time was:     00:00:09.17
CPU time was:         00:00:00.03

Please tell me where I am going wrong?, I cannot display the data from Incl_C.dat.

Might be a duplicate of [another question](http://stackoverflow.com/questions/3484952/sql-loader-script-help-with-adding-sysdate-user) — Thomas Mueller, Mar 27 '13 at 14:36
the problem is not with TRAILING NULLCOLS here, its the sql loader error: non-data dependent ORACLE error occurred!. I can't display the Includes_C table data — Navy, Mar 27 '13 at 14:43
Maybe you have pressed [Ctrl+C] afterwards? Because the message says "SQL*Loader-2028: load discontinued to user interrupt (Ctrl-C)" and then "ORA-01013: user requested cancel of current operation" — Thomas Mueller, Mar 27 '13 at 14:45
So, you didn't actually get an error, but it took a long time: 9 seconds. Maybe it's just slow? How large is the data file? 9 seconds isn't much, maybe the client is waiting for the server. Who knows. — Thomas Mueller, Mar 27 '13 at 15:04
data file is 316 B, but its more than 9 seconds, its been 4 min and counting! the other data file is 465 B but it loads immediately — Navy, Mar 27 '13 at 15:16
Have you got any pending changes on the `includes_c` table that might be causing SQL*Loader to block waiting for a commit/rollback? E.g. if you're deleted everything but it doesn't know it can insert yet because it won't know if unique constraints might be violated? — Alex Poole, Mar 27 '13 at 15:23
Well, you need to find out why it's hanging, and if it isn't pending data in another session then I'm not sure what could be causing that kind of delay, even if there are triggers etc. (Can you insert records manually, with the same data?) The errors you've shown are irrelevant since they come from your control-c interrupt, so I don't think there's anything for us to go on unfortunately. — Alex Poole, Mar 27 '13 at 15:32
ok I will try inserting the data manually and see what happens, thanks for the help! — Navy, Mar 27 '13 at 15:37
Next time when you ask a question, it would be nice if you just provide that info at the beginning: the import file is very small, it hangs, you have pressed Ctrl+C,... Try to think like somebody who reads your question the first time. — Thomas Mueller, Mar 27 '13 at 15:44

score 0 · Answer 1 · edited Jun 19 '13 at 03:00

It seems the tablespace is full.

Here is my code:

  select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2)||'%' "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc

score 0 · Answer 2 · edited Aug 21 '23 at 14:36

0

This also happened to me when the Tablespace which contained the index of my table in which I uploaded data was FULL. I extended that Tablespace and error went away.

edited Aug 21 '23 at 14:36

Adriaan

17,741
7
42
75

answered Aug 21 '23 at 14:35

Pierre-Jean MEUNIER

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34875417) – GuedesBF Aug 26 '23 at 00:13

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

2 Answers2