while upgrading ArcSDE Geodatabase from version 9.3.1 SP2 to version 10 SP5, the below error was faced:

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

This error indicates that you are missing an indextype in your ArcSDE Geodatabase in Oracle, the indextype name is: “ST_SPATIAL_INDEX”

when attempting to execute the following Sql Query to create the index type:

*******************************************************************************************************

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR “SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER), “SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER), “SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2), “SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),”SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)USING “SDE”.”ST_DOMAIN_METHODS” WITH LOCAL RANGE PARTITION;

**********************************************************************************************************

the following error was received:

This error indicates that for some reason there are missing operators in your ArcSDE geodatabase, you need to create them.

but, first you need to query the complete list of operators from another ArcSDE geodatabase that is intact and healthy, using the query: select * from user_operators;

compare the list of operators where the ArcSDE geodatabase that has the upgrade issue, and create the missing ones.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

after creating all missing operators, try executing the previous sql query mentioned: “CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX”

then use the following sql, to check indextype was created:

select * from user_indextypes;

Re-Run the Upgrade, and it will be successful.

Query Reference:

select * from user_operators;

select * from user_indextypes;

select dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’) from dual;

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

If this is not the case you are facing….but facing the same error then try the following:

SQL> GRANT EXECUTE ON sde.st_spatial_index TO SCHEMA_NAME;

OR

SQL> GRANT EXECUTE ON sde.st_spatial_index TO PUBLIC;

AND

SQL> GRANT CREATE INDEXTYPE TO SCHEMA_NAME;