Re: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column

I would enable sql_trace and look at the create statement produced for the journalling IOT.
Your 2011 is wrong, it should be 1,111; if (for no good reason) you multiply the column length by 3 (again), the answer comes to 3217.

So, taking a guess, Oracle has taken the data length as 3 x char_length, and then the journal code has defined it's char_length as the result - which has taken ITS data_length to 9 times the original char_length.

Raise an SR if I'm right.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Kudryk, Mark" <Mark_Kudryk_at_ADP.com> To: <oracle-l_at_freelists.org> Sent: Monday, August 29, 2011 5:05 PM Subject: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH MK_TEST_LENGTH_N351 INVOICE_ID NUMBER 22 0

MK_TEST_LENGTH_N351 DATA_FIELD_ID NUMBER 22 0

MK_TEST_LENGTH_N351 ADDITIONAL_DATA_N351 NVARCHAR2 1053 351

Using the formula above, I calculate the key length to be 1053 (v_additional_data) + 22 (invoice_id) + 22 (data_field_id) + 3 (number of columns) + 2 (length of key) + 8 (ROWID) + 1 (length of rowid) = 2011, which is not close to 3215. As we are using UTF8, I would have expected that the number of nvarchar2 characters I could have used would have been 1/3 of a varchar2 column (3155 vs. 1031), and not approximately 1/9th (350) the size.

--
http://www.freelists.org/webpage/oracle-l