HOW TO

Determine if the st_geometry type in Oracle is the same in two different Oracle instances

Summary

Prior to ArcGIS 9.3, when creating a geodatabase in Oracle, and the st_geometry type and subtypes are created, their type OID values were not explicitly set. Without setting a defined value, Oracle will randomly generate a type OID value (which will be different in all Oracle instances).

Starting with ArcGIS 9.3, when a geodatabase and the st_geometry type are created, ArcGIS now specifically sets the type OID value.

If the st_geometry type OID values are not the same between Oracle instances, a table with an st_geometry attribute cannot be exported and the data cannot be imported into a second instance (because the type definition is technically different). There is a known limitation in Oracle that one cannot ALTER a type's OID value after it has been defined.

Procedure

To list the types and their OID values, execute the following SQL statement in SQL*Plus as the SDE user.
Code:
SQL> SELECT type_name, type_oid
2 FROM user_types
3 ORDER BY type_name;

    Article ID:000010830

    Software:
    • Legacy Products

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options

    Related Information

    Discover more on this topic