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

1495
3
Jump to solution
12-21-2022 11:28 AM
robert_at_work
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

0 Kudos
3 Replies
MarceloMarques
Esri Regular Contributor

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos
robert_at_work
New Contributor III

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

 

0 Kudos
MarceloMarques
Esri Regular Contributor

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

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos