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

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:

ORA-29830: operator does not exist
ORA-29830: operator does not exist

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;

Image

 

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;

ST_SPATIAL_INDEX indextype
ST_SPATIAL_INDEX indextype

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;

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s