31 August 2013

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

load.ctl  parameter file content :

INFILE dummy_table.txt
BADFILE dummy_table.bad
DISCARDFILE dummy_table.dsc
INTO TABLE dummy_table_length_is_more_than_30


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

SQL*Loader: Release - 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


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.

01 April 2013

"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 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 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)


After fixing this error, we got another error:

[TimesTen][TimesTen 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


After giving enough disk space to the filesytem, error is gone.
                   58490880 4660048 50466552    8% /timesten