ORA-25194: invalid COMPRESS prefix length value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

While executing a DDL statement like:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
) ORGANIZATION INDEX COMPRESS 3

or:

CREATE INDEX test_idx ON test_table(key_a, key_b, key_c) COMPRESS 3

You've got:

ORA-25194: invalid COMPRESS prefix length value

You need to remove the compression prefix (3 in the example above) after the COMPRESS clause or specify a compression prefix number that is less or equal than 2:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
) ORGANIZATION INDEX COMPRESS -- this is equivalent to COMPRESS 2 in this case

or:

CREATE INDEX test_idx ON test_table(key_a, key_b, key_c) COMPRESS 1

More details about index compression and applicable cases available in a previous posting.

See message translations for ORA-25194 and search additional resources