SQL*Loader-704: Internal error: ulmtsyn and ORA-01480

load.ctl  parameter file content :

LOAD DATA

INFILE dummy_table.txt

BADFILE dummy_table.bad

DISCARDFILE dummy_table.dsc

TRUNCATE 

INTO TABLE dummy_table_length_is_more_than_30

FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS(  msisdn )

Execution

D:mp>sqlldr mennan/mennan@ora11gr2 control=load.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Fri Aug 2 11:52:37 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulmtsyn: OCIStmtExecute(tabhp) [1480]

ORA-01480:  trailing null missing from STR bind value

D:mp>

The error is because of table name(dummy_table_length_is_more_than_30) length is 34 which is not allowed in oracle(the internal error that is thrown, is not meaningful in my opinion). Please note that, every object shall have name  is less than 30 chars. ( I do not know why this restriction exists in Oracle in 21st centry :). I hope, Oracle will remove this restriction in object names).

Code Instrumentation

A few days ago, I have given a presentation about code instrumentation to my colleagues. Instrumentation is something that shall not be an optional issue; shall be a quality concern.

A slide  from the presentation:

How to Instrument

Use database tables for logging, tracing and performance counters. Archive these log information, when it is not frequently analyzed. If not necessary, erase these table data, otherwise DBAs will complain.
It can also be used file system(utl_file package) for logging. But note that, database tables are very easy to analyze and query.
Use Oracle’s built-in tools for performance monitoring and profiling. (Statspack, AWR reports, PL/SQL Hierarchical Profiler etc)
Only log, necessary information. These log records are read by human eyes. Tons of log information are very difficult to read and analyze.
Please analyze the log and trace information. Be proactive. Take actions before something goes wrong. 
Instrumentation shall be a quality issue and shall not be skipped. It is a code discipline.
In Code Review process, reviewer shall mark un-instrumented code.

"TT0802: Data store space exhausted" and "TT0778: Log write failed because filesystem is full" errors

A few days ago, we have encounter a timesten error:

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT0802: Data store space exhausted -- file "blk.c", lineno 3260, procedure "sbBlkAlloc"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 802

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT6220: Permanent data partition free space insufficient to allocate 5640 bytes of memory -- file "blk.c", lineno 3260, procedure "sbBlkAlloc"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 6220

When we analyze, we see that the PermSize parameter is insufficient for loading database into the memory.  We update the PermSize in the sys.odbc.ini file and restart timesten daemon.(you can read this post for restarting timesten daemon)

sys.odbc.ini

[tt01]

…

PermSize=51200

….

After fixing this error, we got another error:

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT0778: Log write failed because filesystem is full -- file "logmgr.c", lineno

6065, procedure "sbLogRecInsert"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 778

This error is because of DataStore path have not enough space as the parameter PermSize.

$ df -k .

/timesten         (/dev/lvtimesten) :  5205785 total allocated Kb

                                                 558788 free allocated Kb

                                                 4646997 used allocated Kb

                                                    90 % allocation used

sys.odbc.ini

[tt01]

..

DataStore=/timesten/TimesTen/tt1121/DataStore/ttds

…

PermSize=51200

After giving enough disk space to the filesytem, error is gone.

/dev/lvtimesten

                   58490880 4660048 50466552    8% /timesten