ORA-29833: indextype does not exist

ORA-29833: indextype does not exist

Environment: 

Oracle version: Oracle 10gR2 (10.2.0.4.0 )

OS version: RHEL 6.2 

In my environment, there was a database migration as the hardware and OS is upgraded.  So I installed Oracle 10g in the new server and created a database with the same name(as existing database). I tried to export the existing database and imported in the new database. While comparing the new database schemas with the existing schemas, there are some indexes missing in the new database schema.

                I tried to import that schema again, but it is of no use. I tried to create them manually. Then I encountered with the below error:

SQL> CREATE INDEX CPSI.TRACKS$TRACK_NAME ON CPSI.TRACKS   2  (TRACK_NAME)   3  INDEXTYPE IS CTXSYS.CONTEXT   4  PARAMETERS('sync'); ERROR at line 2:  ORA-29833: indextype does not exist

Cause:

There is no index type by the specified name. and my database is not installed with Oracle Text. We can find this by checking if the database has CTXSYS default user.

More info about text index

Solution

This can be resolved by installing Oracle Text.

Issue:

------

Step 1: Ensure the schema CTXSYS does not exist, if exists ignore the execution of “step 2”. 

Step 2: Add the below mentioned statement above the existing statement(also mentioned below) in the catctx.sql file which is in the $ORACLE_HOME/ctx/admin" location.

Existing statement : Rem CTXDEF.sql - ctx default object creation

Statement to be added: grant execute on CTX_DDL to ctxsys ;

In SYS user login, execute the script to create CTXSYS schema and its related objects after confirming below parameters to this script

ctxsys - password of CTXSYS schema

SYSAUX                - default tablespace

TEMP - default temp tablespace (Replace if your database default temporary tablespace is different)

SQL>@$ORACLE_HOME/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

Step 3: In SYS user login execute the below script to recompile user objects

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 4: In CTXSYS user login, execute the below script to create default preference for language ENGLISH

SQL>@$ORACLE_HOME/ctx/admin/defaults/drdefus.sql

Step 5 : In SYS User login ,execute the below script to give grant permission

SQL>grant ctxapp to DBusername identified by Password; --( username CPSI in my case) SQL>grant select on ctxsys.dr$preference to DBusername; --( username CPSI in my case) SQL>grant execute on CTX_DDL to DBusername; --( username CPSI in my case)

Step 6: Login as DB user and execute the below drop index script if any already exists in same name

SQL>DROP INDEX LEI_CATALOG_SEARCH_SFIELD

Step 7: Login as DB user and create the below domain index

SQL> CREATE INDEX CPSI.TRACKS$TRACK_NAME ON CPSI.TRACKS   2  (TRACK_NAME)   3  INDEXTYPE IS CTXSYS.CONTEXT   4  PARAMETERS('sync');

Make sure that you should get the following output if you run the below queries(if your version is 10.2.0.4.0 )

SQL> select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT'; COMP_NAME                    STATUS   VERSION ---------------------------- -------- ---------- Oracle Text                  VALID    10.2.0.4.0 SQL> SQL> select * from ctxsys.ctx_version; VER_DICT   VER_CODE ---------- ---------- 10.2.0.4.0 10.2.0.4.0 SQL> SQL> select substr(ctxsys.dri_version,1,10) VER_CODE from dual; VER_CODE ---------- 10.2.0.4.0 SQL> select count(*) from dba_objects where owner='CTXSYS';   COUNT(*) ----------        313 SQL> SQL> -- Get a summary count SQL> select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type order by 1; OBJECT_TYPE           COUNT(*) ------------------- ---------- FUNCTION                     2 INDEX                       46 INDEXTYPE                    4 LIBRARY                      1 LOB                          1 OPERATOR                     6 PACKAGE                     67 PACKAGE BODY                56 PROCEDURE                    2 SEQUENCE                     3 TABLE                       37 TYPE                        27 TYPE BODY                    7 VIEW                        54 14 rows selected. SQL> -- Any invalid objects SQL> select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name; no rows selected SQL> SQL> spool off

Refer DOC ID:979705.1 for more info in metalink