This error message can be returned when an unexpected clause is added in a primary constraint specification for an index organized table:
CREATE TABLE test_table (Two facts must be noted here:
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) USING INDEX COMPRESS
) ORGANIZATION INDEX
/
ORA-25176: storage specification not permitted for primary key
the "USING INDEX" clause above becomes perfectly legitimate if we remove the COMPRESS keyword or if we move it after the ORGANIZATION INDEX clause, as already explained in a similar situation occurring in Oracle XE, where a different error message is returned (see ORA-14071).
Likewise, if you remove ORGANIZATION INDEX, the COMPRESS clause will be accepted without problems:
CREATE TABLE test_table (In conclusion, you just picked a wrong syntax, if you want to enable compression on an index organized table, then change the statement as follows:
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
constraint pk_test_table primary key(key_a, key_b, key_c) USING INDEX COMPRESS 2
)
CREATE TABLE test_table (As already mentioned in a previous posting, you can also specify COMPRESS n, where n is a number lesser than the number of key columns and in this specific case you could specify either COMPRESS 1 or COMPRESS 2, because the key is made up of three columns.
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) USING INDEX
) ORGANIZATION INDEX
COMPRESS -- see the note below
/
See message translations for ORA-25176 and search additional resources
No comments:
Post a Comment