I want to set the tablespace for all oracle text generated tables and the indexes. Managed to get that for most but not for all.
-- drop table whs_base_temp cascade constraints purge;
create table whs_base_temp
(
id number constraint whs_base_temp_id_pk primary key using index tablespace ts_whs_index
, title varchar2(400) constraint whs_base_temp_title_nn not null
, description varchar2(2000)
, submitters varchar2(200)
/* ... other columns ..*/
)
tablespace ts_whs_data
;
begin
ctx_ddl.drop_preference ('whs_base_storage');
ctx_ddl.drop_preference ('whs_base_datastore');
ctx_ddl.drop_section_group('whs_base_section_group');
end;
-- Not sure if I am setting up the following correctly...
begin
ctx_ddl.create_preference ('whs_base_storage', 'BASIC_STORAGE' );
ctx_ddl.set_attribute ('whs_base_storage', 'STAGE_ITAB', 'true' );
ctx_ddl.set_attribute ('whs_base_storage', 'g_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'g_index_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'i_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'i_index_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'K_TABLE_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'K_INDEX_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'N_TABLE_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute ('whs_base_storage', 'U_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.create_preference ('whs_base_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('whs_base_datastore', 'COLUMNS','title,description,submitters');
ctx_ddl.create_section_group('whs_base_section_group', 'BASIC_SECTION_GROUP');
ctx_ddl.add_field_section ('whs_base_section_group', 'title', 'title', true);
ctx_ddl.add_field_section ('whs_base_section_group', 'description', 'description', true);
ctx_ddl.add_field_section ('whs_base_section_group', 'submitters', 'submitters', true);
end;
--drop index whs_base_temp_ft_idx;
CREATE INDEX whs_base_temp_ft_idx ON whs_base_temp(title)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
('storage whs_base_storage datastore whs_base_datastore section group whs_base_section_group sync (on commit)')
;
SQL> select table_name, tablespace_name from user_all_tables order by 1;
TABLE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
DR$WHS_BASE_TEMP_FT_IDX$G TS_WHS_INDEX
DR$WHS_BASE_TEMP_FT_IDX$I TS_WHS_INDEX
DR$WHS_BASE_TEMP_FT_IDX$K TS_WHS_INDEX
DR$WHS_BASE_TEMP_FT_IDX$N
DR$WHS_BASE_TEMP_FT_IDX$U TS_WHS_INDEX
WHS_BASE_TEMP TS_WHS_DATA
6 rows selected.
SQL> select index_name, tablespace_name from user_indexes order by 1;
INDEX_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
DR$WHS_BASE_TEMP_FT_IDX$H TS_WHS_INDEX
DR$WHS_BASE_TEMP_FT_IDX$KD TS_WHS_DATA *(1)
DR$WHS_BASE_TEMP_FT_IDX$KR TS_WHS_DATA
DR$WHS_BASE_TEMP_FT_IDX$X TS_WHS_INDEX *(2)
SYS_C00208440 TS_WHS_INDEX
SYS_IL0000468684C00006$$ TS_WHS_INDEX
SYS_IL0000468692C00006$$ TS_WHS_INDEX
SYS_IOT_TOP_468688 TS_WHS_INDEX
WHS_BASE_TEMP_FT_IDX
WHS_BASE_TEMP_ID_PK TS_WHS_INDEX
10 rows selected.
This is my first attempt to work with oracle text. I may have got few things wrong here (please feel free the correct), but how to get
*(1) to point to ts_whs_index?
*(2) if it is possible to set the name for indexes starting with SYS_*?
Thanks