Moving Oracle Text Index

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;

You can even do it online for most indexes:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow thw above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

* DR$[index_name]$I
* DR$[index_name]$K
* DR$[index_name]$N
* DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');