Siebel Tools >  ORA-01450 Error Message - When performing DB Init task for Oracle XE Local Database for IP16.0 Version

ORA-01450 Error Message - When performing DB Init task for Oracle XE Local Database for IP16.0 Version

APPLIES TO:
Siebel CRM - Version 16.0 [IP2016] and later

SYMPTOMS
Having issues with Local database DB Init task after upgrading to Siebel IP16.0

Local database DB Init fails fails with the following error messages:

UpgradeLog UpgradeError 1 0000000257b12d70:0 2016-08-15 17:23:52 ODBC Error: Unable to connect to ODBC Data Source (LOCAL_XE) using Login (SIEBEL).
SQLError Statement 0 0000000257b12d70:0 2016-08-15 17:24:18 SQL Statement:
create unique index CX_EAI_WORKFLOW_U1 on CX_EAI_WORKFLOW
("NAME", "TYPE", "PARENT_ROW_ID", "WERT") parallel nologging
DBCLog DBCLogError 1 0000000257b12d70:0 2016-08-15 17:24:18 [tp][ODBC Oracle driver][Oracle]ORA-01450: maximum key length (6398) exceeded

CAUSE
The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length.

SOLUTION
Firstly, as mentioned before the index cannot be created due to the restrictions of size associated to the size of block in Oracle DB.

Now coming to issue / scenario in this particular customers case = the sum of the size of the columns that are part of the index (255 + 255 + 255 + 1000) would be much below the size of the block 8k, but still fails .

So in regard to the above here is the explanation for your kind attention please:

In a code page that uses one byte as we8mswin1252 , a column varchar2(10 bytes) will use 10 bytes. A varchar2(10 char) will use 10 bytes.

But in unicode , a column varchar2(10 bytes) will still use 10 bytes but a column varchar2(10 char) can use up to 40 bytes.

In Siebel , the varchar2 columns are created in ‘char’ and not bytes. This means that the size will vary according to their characterset in use.

Hence, as in Oracle XE the characterset is AL32UTF8 and the block size is 8 , this will both limit the size of the index to be created.

As this is custom extension table with custom index, we would say that it can be considered as is a limitation and not a bug. So going forward to address this reported behavior customer may need to review the applicable customizations so that it fits within this limitation.

Secondly, on further investigation customer identified two problematic indexes which was causing issue in this reported scenario

For documentation purpose:
>> how to figure out the potentially problematic indexes:

select sum(t2.length), t3.name IndexName from S_INDEX_COLUMN t1, s_column t2, s_index t3
where t1.COL_ID = t2.row_id
and t1.index_id = t3.row_id
group by t3.name
order by sum(t2.length) desc

>> In this scenario customer have two indexes with length > 1700
>> Customers development team will have to check the custom indexes in question and accordingly take the preventive steps.

DB Init task worked as expected after they inactivating the two problematic indexes.