Dear All,

Currently I am working on Oracle 10g (10.2.0.4 Enterprise varsion). It is upgraded from Oracle 9i(9.2.0.6 Enterprise
). Operating system is Linux.


I am facing many errors while creating a Text index on a table. While executing a procedure which drops and creates text index on table, first it raised following Error.
ORA-29868:Cannot issue DDL on a domain index marked as LOADING.

So I used the command to drop this index with FORCE as
DROP INDEX FORCE IX_INDEXNAME;

Again compiling the procedure and executing it, many errors raised as
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04065: not executed,altered or dropped stored procedure "PUBLIC.UTL_RAW"
ORA-06508: PL/SQL Could not find program unit being called "PUBLIC.UTL_RAW"
ORA-06512: at "CTXSYS.DRIPARSE" line 41
ORA-06512: at "CTXSYS.DRIPARSE" line 1710
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS" line 81

Then I checked whether UTL_RAW package is available and valid or not with sql query
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE OBJECT_TYPE IN('PACKAGE', 'PACKAGE BODY') AND OBJECT_NAME = 'UTL_RAW';

Package is available with VALID status in database.

Then I unlocked CTXSYS account guessing one of the possibility.

ALTER USER CTXSYS ACCOUNT UNLOCK;

All EXECUTE grants on
CTXSYS.CTX_CLS,
CTXSYS.CTX_DDL,
CTXSYS.CTX_DOC,
CTXSYS.CTX_OUTPUT,
CTXSYS.CTX_QUERY,
CTXSYS.CTX_REPORT,
CTXSYS.CTX_THES,
CTXSYS.CTX_ULEXER,
CTXSYS.CONTEXT provided to the user.

Executed the script PRVTRAWB.PLB available at ORACLE_HOME\RDBMS\ADMIN\PRVTRAWB.PLB for UTL_RAW package body.

Again while execution, procedure raised number of errors.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04065: not executed,altered or dropped stored procedure "PUBLIC.PLITBLM"
ORA-06508: PL/SQL Could not find program unit being called "PUBLIC.PLITBLM"
ORA-06512: at "CTXSYS.DRVUTL" line 51
ORA-06512: at "CTXSYS.DRUE" line 144
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS" line 364
ORA-06510: PL/SQL:Unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDDL" line 702
ORA-06512: at "CTXSYS.DRIXMD" line 1720
ORA-00001: unique constraint (CTXSYS.DRC$IDX_COLSPEC) violated

Please, forward the solution or even hint to find out the way to resolve this issue.

Thank You,

ukupatil
 

Please take a look at note 370137.1 "After Upgrade, Some Packages Intermittently Fail with ORA-04065" at support.oracle.com.  It suggests that you should run $ORACLE_HOME/rdbms/admin/utlirp.sql  To do this you will need to start the database in upgrade mode and login as sys.  The comments in utlirp.sql also indicate that you need to be in the $ORACLE_HOME/rdbms/admin directory when executing utlirp - utlirp references the scripts standard.sql and dbmsstdx.sql.  Then shutdown and restart your database in normal mode and run utlrp.sql to recompile invalid objects.