ORA-29850: invalid option for creation of domain indexes

ORA-29850: invalid option for creation of domain indexes

I got this error trying to specify the tablespace for my text indexes. I was moving them from a default tablespace because they are taking up quite a lot of space.

Looking up oerr, you get:
Cause:
The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes clause.
Action:
Choose one of the valid clauses for creation of domain indexes.

You can’t specify a tablespace as such in the create index statement. Something I would have found out had I been arsed to rtfm in the first place.

Anyway, as per Oracle note 150453.1 , you have to create a preference for the storage, and then use the parameter syntax when you create the index, as follows:

begin
Ctx_Ddl.Drop_Preference ( ‘eservice_text_storage’ );
end;
/
begin
Ctx_Ddl.Create_Preference(‘eservice_text_storage’, ‘BASIC_STORAGE’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘I_TABLE_CLAUSE’,
‘tablespace text_indexes’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘K_TABLE_CLAUSE’,
‘tablespace text_indexes’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘R_TABLE_CLAUSE’,
‘tablespace text_indexes storage (initial 1M) lob (data) store as (cache)’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘N_TABLE_CLAUSE’,
‘tablespace text_indexes’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘I_INDEX_CLAUSE’,
‘tablespace text_indexes compress 2’);
ctx_ddl.set_attribute(‘eservice_text_storage’,
‘P_TABLE_CLAUSE’,
‘tablespace text_indexes’);
end;
/

drop index sc_sol_textidx_desc;
create index sc_sol_textidx_desc on owner.sc_solutions(description)
indextype is ctxsys.Context
parameters (‘storage eservice_text_storage’);
;

drop index sc_sol_textidx_dref;
create index sc_sol_textidx_dref on sc_solutions (document_ref)
indextype is ctxsys.context
parameters (‘storage eservice_text_storage LEXER ctxsys.mylex’);

Advertisements