Thursday, November 26, 2009

ORA-29855 error in oracle and solution

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code


- wish this helps you too..

2 comments:

  1. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Oracle soa certification

    ReplyDelete
  2. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles. OSB Online Training

    ReplyDelete