[Error 8505] [SQLState HY000] LOB ID could not be generated


[Error 8505] [SQLState HY000] LOB ID could not be generated

I am a developer and a newbie to teradata.

I am trying to load a large table from my oracle database to teradata 13.0.

I have written a JDBC utility to fetch from my Oracle Instance and Load into teradata database.

The oracle table has 4 CLOB columns (it is a125 columns table) and has 100000 + rows.
My program errors out after inserting 84422 records with the following error:

Exception in thread "main" com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC] [Error 8505] [SQLState HY000] LOB ID could not be generated
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:288)
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:102)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:285)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:176)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:108)
at com.teradata.jdbc.jdbc_4.statemachine.PreparedStatementController.run(PreparedStatementController.java:47)

In my teradata table definition I have created the CLOB columns as :


Any pointers on how to get rid of this error ?

Thanks in Advance


Tags (4)
Teradata Employee

Re: [Error 8505] [SQLState HY000] LOB ID could not be generated

Error 8505 is a Teradata Database error.

Here is what the Teradata Database documentation says about this error.

8505 LOB ID could not be generated

Explanation: A unique LOB identifier could not be
generated because there are no more unused IDs for the
specified key.

Generated By: lobCreate

For Whom: Site support representative or customer.

Notes: A LOB identifier is composed of a key based on
the primary index of the table and an additional 16 bits
for uniqueness. This error indicates that all the possible
uniqueness values are currently in use. It could occur if
the table that contains the LOB column has more than
65534 rows with the same primary row hash value.

Remedy: Redesign the table so that its primary index
has fewer rows with the same primary index value.

Re: [Error 8505] [SQLState HY000] LOB ID could not be generated

If it errored out after so few rows, your choice (explicitly or by default) of primary index column must be extremely poor. This would be a problem in any case--you may notice that the table has a very skewed distribution--but with LOBs you come up against a hard limit of about 65,000 rows per primary index value.

Re: [Error 8505] [SQLState HY000] LOB ID could not be generated

Looks like my primary index was the problem.
I redefined my primary index to add more columns.
the load just passed the point where it used to fail earlier.
Thanks folks !!!