Re: ora-1450 even though max key length is well below the max

Anurag Varma wrote:
Chuck wrote:
Gints Plivna wrote:
Single byte character set on both databases - WE8ISO8859P1
Yea but you said that error message gave you 3118. But looking at your
table definition -length of both varcahr2 columns together (2000 +
2000) is more than 3118.
So at least the error message is completely justifiable.

Gints Plivna
http://www.gplivna.eu/

You can't determine key length by adding the max defined size of the
columns. To determine key length you must query
max(length(col1)+length(col2)...) + number of columns.

See metalink note # 136158.1.

Chuck,

For the index creation, oracle will consider the total key length as
the sum total of defined key lengths.

If max it allows for your case is 3118 bytes, and you have
defined columns being indexed of total size > 4000 bytes.

That said, 3118 limit seems to be for a 4K block size. Are you sure
that your block size is 8K?

Here is an example:

Oracle 10.2.0.2 on Linux

SQL> show parameters db_block_size

NAME TYPE VALUE
------------------------------------ ----------
------------------------------
db_block_size integer 8192
SQL> select value from NLS_DATABASE_PARAMETERS where parameter =
'NLS_CHARACTERSET';

VALUE
---------------------------------------------
WE8ISO8859P1

SQL> create table Y (a date, b varchar2(4000),c varchar2(4000));

Table created.

SQL> create index Y_IDX on Y (a,b,c);
create index Y_IDX on Y (a,b,c)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


I did not even have to insert a single row. The limit is on
the total of defined column sizes!

Anurag


At carumba.

Does anyone around here read? I'll say it one last time... this works in
a 9205 instance with the exact same DDL. If as you say it's based on the
max defined width of the varchars how does this work on 9205?

desc mytable

Name Null? Type
----------------------------- -------- --------------------
LOGDATE NOT NULL DATE
URI VARCHAR2(2000)
REFERRER VARCHAR2(2000)
PAGEVIEWS NUMBER(38)

CREATE UNIQUE INDEX MYINDEX_U1 ON MYTABLE
(LOGDATE, URI, REFERRER)
COMPRESS 2;

Index created.
.

Relevant Pages

  • Re: cannot convert between unicode and non-unicode data types
    ... I am trying to go from NChar 9 in Oracle to a Sql database field datatype ... product can't actually produce a meaningful error message. ... Tried dumping to a raw file but the output is still unicode when I try to ...
    (microsoft.public.sqlserver.dts)
  • cannot convert between unicode and non-unicode string data types
    ... Cindy, I have the same problem, there is a solution, In the OLE DB Destination component, in the Mappings option in the input column you must choose the Data Conversion., and that's it. ... I am converting a simple (at least in the SQL Server 2000 DTS days) to this ... error message stays the same ... numerous times I have inserted the transformation task between the oracle ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Timeout Error
    ... "Robert Lakinski" wrote: ... but you can access data in Sql server via MS ... I don't know why I got the error message. ...
    (microsoft.public.sqlserver.server)
  • Re: cannot convert between unicode and non-unicode data types
    ... Mine is also Char 9 in Oracle, using the data transformation of dstr does not ... product can't actually produce a meaningful error message. ... oracle and the sql database. ...
    (microsoft.public.sqlserver.dts)
  • Re: Multilingual versions of SQL Express 2005 available ? Where ?
    ... The error code alone is not sufficient. ... error code which is represented in the parameters used to format the SQL ... language is used. ... > we may consider provide localized error message according to the error ...
    (microsoft.public.sqlserver.setup)
Loading