nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

robert_at_work

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

Find more posts tagged with

Enterprise geodatabase

Databases

Geodatabase Administration

Accepted answers

MMBR

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.

All comments

MMBR

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

Esri Support Search-Results - ORA-29833 Data Pump

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

robert_at_work

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

MMBR

@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,

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.