English

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;

    Related Information