Hi Tom,
I am trying to create domain index on table, I am getting
ORA-29850: invalid option for creation of domain indexes. Can you please explain why can't we create domain index with nologging option.
SQL> begin
2 ctx_ddl.drop_preference('EmployeeSearch');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> Drop Index IDX_EmployeeEmpID;
Index dropped.
SQL>
SQL> Begin
2 ctx_ddl.create_preference('EmployeeSearch','user_datastore');
3 ctx_ddl.set_attribute('EmployeeSearch','procedure','ContextSearchfilter.EmployeeSearch_Filter');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> create index IDX_EmployeeEmpID on Employee(EmpID) indextype is ctxsys.context
2 parameters('lexer jadoo_lexer datastore EmployeeSearch sync (on commit)') Nologging;
parameters('lexer jadoo_lexer datastore EmployeeSearch sync (on commit)') Nologging
*
ERROR at line 2:
ORA-29850: invalid option for creation of domain indexes
Thaks in advance,
S Ranga Prasad.
You do this by setting storage parameters. You need to set this for each of the various tables Oracle Text creates.
First you need to create the storage preference. Then assign the storage attributes to this:
begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
'nologging');
ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE',
'nologging');
end;
/
You can also use this to change the tablespace etc.
With this in place, specify this preference in the storage parameter:
create table employee (
empid varchar2(1000)
);
create or replace procedure employeesearch_filter (
r in rowid,
c in out nocopy varchar2 )
is
begin
null;
end;
/
create index IDX_EmployeeEmpID on Employee(EmpID) indextype is
ctxsys.context
parameters('datastore EmployeeSearch storage mystore sync (on commit)') ;
select table_name, logging from dba_tables
where table_name like 'DR$%$%'
and owner = user;
TABLE_NAME LOG
------------------------------ ---
DR$IDX_EMPLOYEEEMPID$R NO
DR$IDX_EMPLOYEEEMPID$N
DR$IDX_EMPLOYEEEMPID$K
DR$IDX_EMPLOYEEEMPID$I NO
select index_name, logging from dba_indexes
where index_name like 'DR$%$%'
and owner = user;
INDEX_NAME LOG
------------------------------ ---
DR$IDX_EMPLOYEEEMPID$X NO