Problem: Update to the issue with Oracle patch version 10.2.0.4 may make unexpected changes to the ST_GEOMETRY schema
When using the ESRI ST_Geometry type, the creation or upgrade of an ArcGIS Server geodatabase at version 9.2 creates PUBLIC synonyms for ESRI’s ST_Geometry type. Installing Oracle 10.2.0.4, using Oracle Spatial, removes any PUBLIC synonym with 'ST_' and replaces them with the same name, owned by Oracle Spatial, using the qualifier 'MDSYS'. For example, ST_Geometry and ST_Curves become MDSYS.ST_Geometry and MDSYS.ST_Curves.
What products are affected?
ArcGIS Server 9.2 Enterprise Edition when the ST_Geometry type is used with Oracle 10g R2 Patch Set 10.2.0.4. Other ESRI supported 9.2 Oracle spatial storage schemas (the ESRI Binary – LONG RAW, the ESRI Binary – LOB, and the SDO_Geometry type of Oracle Locator and Spatial) do not seem to be affected by Oracle 10.2.0.4.
This change was being implemented at Oracle 11g, so the appropriate modifications have been made to account for this in our ArcGIS version 9.3 release. However, the change that Oracle made in version 11g unexpectedly appeared in version 10.2.0.4. Since there was no mention of this change in the Oracle 10.2.0.4 Installation Notes, the modifications could not be made for the ArcGIS 9.2 Service Pack 5 release.
Solution or Workaround
ArcSDE 9.3 for Oracle no longer creates or depends on public synonyms for these SDE_Geometry objects. ArcSDE 9.2 for Oracle has also been updated at Service Pack 6 with these same changes. ArcSDE 9.2 Service Pack 6 was released on July 21, 2008.