How To: Determine if the st_geometry type in Oracle is the same in two different Oracle instances
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.
To list the types and their OID values, execute the following SQL statement in SQL*Plus as the SDE user.
SQL> SELECT type_name, type_oid
2 FROM user_types
3 ORDER BY type_name;
- Importing an Oracle export file with an ST_Geometry attribute encounters the Oracle error ORA-39779
- ST_Geometry types in Oracle do not contain a user-defined OID property value
- What are the expected type OID values for st_geometry in Oracle?