topic Re: Oracle 11g to 19c - ORA-29833: indextype does not exist in Geodatabase Questions

Oracle 11g to 19c - ORA-29833: indextype does not exist

robert_at_work — Wed, 21 Dec 2022 19:28:10 GMT

Hi there,

we migrated from Oracle 11g R2 to Oracle 19c.

While importing into 19c we got the following error message for each spatial index:

ORA-39083: Object type INDEX:"ELE_QS"."A9608_IX1" failed to create with error:

ORA-29833: indextype does not exist

We cant create any spatial index in ArcCatalog either. Non-spatial index are fine.

Oracle has published a document regarding this specific issue - our DB admin looked into it and it is apparently not valid anymore for 19c (Link:

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2559108_1.html).

The DB has been installed with Oracle Multimedia and Oracle Text.

We also installed the patch for ArcMap 10.7.1 required for 19c (Link) - but the data-pump import issue is not related to ArcMap installation itself.

Any ideas?

Many thanks, Rob

Re: Oracle 11g to 19c - ORA-29833: indextype does not exist

MarceloMarques — Wed, 21 Dec 2022 20:00:56 GMT

Please, ask your Oracle Database Administrator and Geodatabase Administrator to read my white paper below.

How to Move the Oracle Enterprise Geodatabase with the Oracle Data Pump Utility

!!! Note!!!

you must import the sde user schema first only then import the other data owner users

this because the esri sde spatial type st_geometry and the arcsde geodatabase repository tables, types, etc. must exist before you try to import the data owner users, especially if the geodatabase has featureclasses that use the esri sde st_geometry spatial type.

support.esri.com

Esri Support Search-Results - ORA-29833 Data Pump

Bug: Importing an Oracle export file with the remap_schema option fails (esri.com)

FAQ: Working with ST_Geometry and Oracle export/import (esri.com)

Note: if you are still not able to resolve the issue then please open a ticket with Esri Technical Support to continue to troubleshoot further.

Re: Oracle 11g to 19c - ORA-29833: indextype does not exist

robert_at_work — Mon, 09 Jan 2023 14:36:29 GMT

Hi Marcelo,

thank you for your reply. It is important to respect the right order when importing. In our case the cause was was slightly different. When importing the SDE user first we got the same error message again - one which I did not post in my original question. That error message in full is:

Failing sql is: CREATE 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", NUMBER, NUMBER, NUMBER, NUMBER), "SDE"."ST_ENVINTERSECTS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"), "SDE"."ST_ENVINTERSECTS" ("SDE"."ST_GEOMETRY", NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2), "SDE"."ST_ENVINTERSECTS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY", VARCHAR2), "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" WITHOUT ARRAY DML WITH LOCAL RANGE PARTITION Processing object type SCHEMA_EXPORT/INDEXTYPE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39112: Dependent object type OBJECT_GRANT:"SDE" skipped, base object type INDEXTYPE:"SDE"."ST_SPATIAL_INDEX" creation failed Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX ORA-39083: Object type INDEX:"ELEAHS"."A3570_IX1" failed to create with error: ORA-29833: indextype does not exist

As it turns out this issue was only resolved after installing/configuring Oracle Spatial. I dont have any further details as this was done by our DB admin. There was also a bit of confusion about Oracle Multimedia and Locator as MM is not supported anymore in 19c but Locator is.

With this issue solved and importing the SDE user first the error message I mentioned above did not occur again.

Regards, Robert

Re: Oracle 11g to 19c - ORA-29833: indextype does not exist

MarceloMarques — Mon, 09 Jan 2023 17:05:44 GMT

@robert_at_work

Glad you figure out the issue.

Oracle database requirements for ArcGIS 10.8.x and ArcGIS Pro 2.5, 2.6, and 2.7—System Requirements | Documentation

"The Oracle Text component must be installed. The Text component is installed by default in Oracle; however, if you did not perform a default installation, the Text component may not have been installed."

Oracle Spatial is not required to be installed if you are using a Geodatabase with Esri ST_Geometry spatial data type and you do not plan to use Oracle Spatial.

Regards,