DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Win NT strange problem

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    I am creating a table and I get this error.
    This is exclusively happening in one NT machine..
    I am able to create the same table on another NT machine and a sun solaris machine..
    one of the fileds is unique and has a length of 2k..
    on the same machine that had a problem if i reduce the size say varchar2(20), it works.......
    what can be done..

    FILE_KEY VARCHAR2(2048) CONSTRAINT UNQ_FILE UNIQUE ,


    CREATE TABLE FILE_DATASOURCE
    *
    ERROR at line 1:
    ORA-01450: maximum key length (758) exceeded

    ORA-01450 maximum key length (string) exceeded

    Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system. The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

    Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system. See also your operating system-specific Oracle documentation.



  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    [QUOTE][i]Originally posted by st2000 [/i]
    [B]The maximum index length varies by operating system.
    [/B][/QUOTE]
    This is probably your problem.
    Jeff Hunter

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Thanks Jeff.. but how do I find it that the index length is something of that sort or how do I solve it..

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think you have already proven it is an index length problem when you created the index on a varchar2(20). What is the length on NT? I don't know.

    The only way you are going to resolve this is by a re-design of the table involved. I would have a table that contains a number as a PK and the file_name as a column. You then change the PK in the file_datasource to the artificial PK in the other table.
    Jeff Hunter

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Jeff:

    I could not interpret your answer on this..

    The only way you are going to resolve this is by a re-design of the table involved. I would have a table that contains a number as a PK and the file_name as a column. You then change the PK in the file_datasource to the artificial PK in the other table.

    Can you explain again differently..

    Thx..

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have a table now:
    FILE_DATASOURCE:
    FILE_KEY VARCHAR2(2048) PRIMARY KEY,
    other fields....

    You would break up FILE_DATASOURCE into:
    FILE_NAMES:
    FILE_ID number(10) PRIMARY KEY,
    FILE_NAME varchar2(2048)

    and
    FILE_DATASOURCE:
    FILE_ID number(10),
    other fields...

    Then, whenever you wanted to get all the information you would have a join on FILE_DATASOURCE and FILE_NAMES via the FILE_ID field.

    For simpler queries, you could create a view called FILE_INFO that was defined as:
    CREATE VIEW file_info AS
    SELECT a.file_name file_key, b.*
    FROM file_names a, file_datasource b
    WHERE a.file_id = b.file_id

    and it would look like your original FILE_DATASOURCE table.


    Jeff Hunter

  7. #7
    Join Date
    Oct 2000
    Posts
    449
    I tried various combos Jeff..
    You are right. It cannot exist as a KEY anymore..
    thanks for your idea..


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width