Thursday, April 4, 2013

Oracle Text Error : DRG-50857: oracle error in drvddl.IndexOptimizeRebuild

When using Oracle text indexes privileges , several privileges are granting to the user owns the indexes. These privileges are enough if you want to create , sync and ptimize the indexes excpet the optimization with rebuild option. This option requires the create trigger option beacuse Optimize Rebuild uses a trigger to capture changes made during the optimize. 

e.g

connect myuser/pass

exec ctx_ddl.OPTIMIZE_INDEX('MY_TEXT_IDX','REBUILD');

BEGIN ctx_ddl.OPTIMIZE_INDEX('MY_TEXT_IDX','REBUILD'); END;

*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexOptimizeRebuild
ORA-01031: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 1042
ORA-06512: at line 1

After granting the CREATE TRIGGER privilege to MYUSER command executed succesfully !!