How to move Oracle context index to another tablespace?

Links: http://www.dbatools.net/experience/move_oracle_context_index.html

    We cannot use following command to move Oracle context index to another tablespace, because it's a special kind of index.

ALTER INDEX ... REBUILD TABLESPACE CTXDATA;

    You need create new storage setting or modify existing storage setting. Following is to create a new one.

begin
ctx_ddl.create_preference('CTXSTORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('CTXSTORE', 'I_TABLE_CLAUSE', 'tablespace CTXDATA');
ctx_ddl.set_attribute('CTXSTORE', 'K_TABLE_CLAUSE', 'tablespace CTXDATA');
ctx_ddl.set_attribute('CTXSTORE', 'R_TABLE_CLAUSE', 'tablespace CTXDATA');
ctx_ddl.set_attribute('CTXSTORE', 'N_TABLE_CLAUSE', 'tablespace CTXDATA');
ctx_ddl.set_attribute('CTXSTORE', 'I_INDEX_CLAUSE', 'tablespace CTXDATA');
ctx_ddl.set_attribute('CTXSTORE', 'P_TABLE_CLAUSE', 'tablespace CTXDATA');
end;
/

    Then you can run the following command to rebuild the Oracle context index.

ALTER INDEX ... REBUILD PARAMETERS 'REPLACE STORAGE CTXDATA';

    Then check the space usage of the tablespace.

Post a comment

« Previous | Main | Next »

Powered by
Movable Type 3.36