Oracle Intermedia Text - 8.1.7

 Well, today I experienced using the Oracle Intermedia Text, so that what i got from it:

• Intermedia Index is from type DOMAIN

• When creating an Intermedia index oracle creates 4-5 tables and an index, all have the DR$ prefix

• A good metalink note about creating intermedia indexes you can found here

• "ORA-29863: warning in the execution of ODCIINDEXCREATE routine" when runing ddl's on the text indexes, this error can occur, with no farther information!. Check the errors table (CTX_INDEX_ERRORS) for more information.

• Do not use ctxsrv for maintaning your indexes, read metalink note: 132689.1 for more information about maintaining intermedia indexes.

• Use ctxsys.ctx_ddl instead of alter index when ever it is possible

• Oracle Intermedia Text FAQ metalink note

• Do not create an oracle intermedia index online, this will result an error for each insert/update/delete on the table, if you wish to do it online

then you can do it by: 1) create the index with nopopulate property, this will create the index with no information 2) update the indexed column on each row in the table to it’s current value 3) sync the index

• Get all index errors

select err_timestamp, err_text from ctxsys.CTX_INDEX_ERRORS order by err_timestamp desc;

• Get all changes made for the indexes that are in pending mode (waits for index sync)

select * from ctxsys.ctx_pending

• Create storage preferences for the oracle text indexes

Begin

Ctx_Ddl.Drop_Preference ('my_text_storage' );

Ctx_Ddl.Create_Preference('my_text_storage', 'BASIC_STORAGE');

ctx_ddl.set_attribute('my_text_storage','I_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','K_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','R_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','N_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','I_INDEX_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','P_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

end;

/

• Create an index with the new storage prefs

create index mytextindex on mytable(data) indextype is ctxsys.context parameters ('storage my_text_storage')