CREATE INDEX fails with "insufficient privilege"
burleighJan 17 2009 — edited Jan 19 2009Hello. I've never used TEXT before and frankly my first experience isn't going well. The long and the short of it is that when I ask 11g to make an index (authenticated to the schema) I get an error stack suggesting privilege problems:
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtabc.create_indexes:TC
ORA-01031: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 97
I've concocted this simple case so you can see my work:
create table a_ctxtest
(
mykey int not null,
mytext varchar2(200),
constraint px_testkey primary key ( mykey )
);
create index itx_test on a_ctxtest( mytext ) indextype is ctxsys.ctxcat PARAMETERS ('STORAGE mystore');
This code, found in this forum, had been run earlier in the session:
begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace users storage (initial 32k)');
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE', 'tablespace users storage (initial 32k)');
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE', 'tablespace users storage (initial 32k) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE', 'tablespace users storage (initial 32k)');
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE', 'tablespace users storage (initial 32k) compress 2');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE', 'tablespace users storage (initial 32k)');
ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE', 'tablespace users storage (initial 32k)');
end;
Hints or even outright commands back toward something that works as expected would certainly be welcome.