8 Replies Latest reply: Jan 1, 2010 3:23 AM by user12115 RSS

    ORA-01450: maximum key length (6398) exceeded

    user12115
      Hi guys,

      I am facing the above error when going to create table with UNIQUE clause as below:

      CREATE TABLE Relations (
      RELATIONID DECIMAL(19) NOT NULL,
      NAME1 VARCHAR2(60) NOT NULL,
      ENTITY1 VARCHAR2(1000) NOT NULL,
      NAME2 VARCHAR2(60) NOT NULL,
      ENTITY2 VARCHAR2(1000) NOT NULL,
      RELATIONTYPE INT NOT NULL,
      PRIMARY KEY (RELATIONID),
      UNIQUE (NAME1,ENTITY1,NAME2,ENTITY2,RELATIONTYPE)
      );

      I am using oracle 10.2.0.4 on CentOS 5.3.

      as search from google and metalink I come to know the issue with the DB_BLOCK_SIZE becuase this table is having default tablespace "USERS" and DB is having 8192 db_block_size. From metalink I come to know that the 8k DB_BLOCK_SIZE having maximum Index key length is 3218 bytes and also give some caluculation for index length as below:

      The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) +the length of the rowid (1 byte). 

      ** in my case
      indexlength = 60+1000+60+1000 => 2120
      length of the key = Don't know how to calculate <<<< please suggest me how to caluculate?
      ROWID= 6*4 (four for each unique index) => 24
      length of rowid= 4 (four for each unique index) => 4

      and the other bytes calculation for ROWID and all but it still not exceed the supported "3218 bytes" I guess, might be I am wrong.



      I have verify below things also:
      - users tablespace is having sufficient space to create this table.
      - I have create the new tablespace (8k block size) and then create this table, it failed to create and raising ORA-01450.


      As a temporary solution what I did is:
      Create one new tablespace with 16k block size and create the above table under the 16k tbs and its created.


      My Query in the below point:


      1- If this same table I created in SYS user then its working fine, its not raising the above error. SYS is having SYSTEM as default tablespace. Is this actually db_block_size issue or something missing in created schemas by "CREATE USERS" syntax?


      2- If I create the same table in different OS like "Solaris" this is able to created in any users without specified different 16k blocksize tablespace. why this soo?

      3- Suggest me how to calculate the "length of the key"?



      If anyone how knows the above 3 query answer suggest me.


      Thanks...
        • 1. Re: ORA-01450: maximum key length (6398) exceeded
          damorgan
          No version number.
          No DDL for the unique constraint.
          And, quite frankly, putting a unique constraint on two VARCHAR2(1000) columns makes remarkably close to no sense.

          Q1. Never, ever, create a table or index as SYS

          We can help you if you post appropriate and necessary information.
          • 2. Re: ORA-01450: maximum key length (6398) exceeded
            P.Forstmann
            Check the character set used by the database. If the character set is a multibyte character set, you should take into account that one character may need several bytes. You can check database character set with:
            select * from nls_database_parameters where parameter like '%SET%';
            • 3. Re: ORA-01450: maximum key length (6398) exceeded
              user12115
              DB character set is : AL32UTF8
              • 4. Re: ORA-01450: maximum key length (6398) exceeded
                Dom Brooks
                That is a multibyte characterset of up to 4 bytes.
                So, a single 1000 char defined column could hold 4000 bytes.

                It's very unusual to have a unique key covering a such columns - any explanation?
                • 5. Re: ORA-01450: maximum key length (6398) exceeded
                  P.Forstmann
                  A character encoded in AL32UTF8 may use 2, 3 or 4 bytes and it's possible that Oracle assumes default size is 4 bytes.
                  See this issue with rebuilding index http://www.pythian.com/news/1111/ora-01450-during-online-index-rebuild/.

                  I would suggest to reduce size of the VARCHAR2 columns if possible.
                  • 6. Re: ORA-01450: maximum key length (6398) exceeded
                    user12115
                    Hi Brooks,

                    this SQL failed in Build process so I can't jump and say that this is unusal to use 1000 chars in unique key. Also please note that this issue occur on Cent OS and on other OS it perfectly works withotu any error.

                    I need some optimal suggestion/answer for this error.

                    Thanks...
                    • 7. Re: ORA-01450: maximum key length (6398) exceeded
                      Jonathan Lewis
                      The basic problem is as described by DomBrooks and others.
                      You character set is variable length up to 4 bytes, so Oracle has to allow up to 4 times the length of any varchar2() columns in an index entry.
                      You have 1000 + 60 + 1000 + 60 characters for a total of 2120 characters, requiring 8,280 bytes minimum.
                      There are a few extra bytes needed
                      22 bytes for the INT column
                      1 length byte for each column in the index of less than 128 bytes (total 3)
                      3 length bytes for each column in the index of 128 or more bytes (total 3 * 2 = 6)
                      6 bytes for the rowid carry in a unique index
                      possibly a couple more for the lock byte and column count, but they might not be included.

                      The documentation you have read is out of date - a single index entry can be roughly 80% of the block size, which is why your 8,320-ish key is too big for an 8KB block size, but fits in a 16KB block. (The limites are roughly 6,400 bytes and 12,800 bytes respectively).

                      The puzzle is why you can create the index in the SYS schema, and on other O/Ses.

                      Two ideas to check:

                      I think (but would have to confirm) that the column definition depends on the setting of nls_length_semantics. If I am right then it is possible that in the other cases you have BYTE semantics by default, but in the failing case you have CHAR semantics. So your varchar2(1000) defaults to varchar2(1000 bytes) on the other databases (and the SYS schema), but varchar2(1000 CHAR) in this database.

                      It's possible that the SYS schema behaves differently from the others because of a logon trigger that changes the semantics - but it's just possible (again, I'd have to check) that the SYS schema always uses US7ASCII for varchar2() columns because it has to hold the data dictionary.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                      {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                      fixed format
                      .
                      
                      
                      "Science is more than a body of knowledge; it is a way of thinking" 
                      Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                      • 8. Re: ORA-01450: maximum key length (6398) exceeded
                        user12115
                        Hi All,

                        Thanks for your reply.

                        finally I got the solution and that is I discovered with NLS_LENGTH_SEMANTICS parameters. There is a bug rerpored by oracle for this parameter.

                        Because I change this parameter value from CHAR to BYTE using "ALTER SYSTEM SET NLS_LENGTH_SEMANTICS='BYTE' scope=both, but this will only take effect when I restart the database. otherwise it showing me as "BYTE" from show parameter but actually it won't work as BYTE.

                        Yesteray restart the database and it works fine.


                        Thanks to all....