Pages

Tuesday, 18 September 2012

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.


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

2 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This is really attention-grabbing, You are an overly skilled blogger.
    I've joined your feed and stay up for in quest of more of your magnificent post.
    Additionally, I've shared your site in my social networks

    Feel free to visit my site home

    ReplyDelete