English

Problem: Exporting and importing an Oracle SDE schema does not include public synonyms

Description

When exporting the SDE schema in Oracle, public synonyms are not present when the export file is imported in the target Oracle database.

Without the public synonyms, ArcGIS and SQL applications encounter errors when executing SQL, which reference ST_Geometry objects indicating the object does not exist.

Cause

Public synonyms are not owned by a schema, they are public. Therefore, when exporting the SDE schema, the public synonyms that were created by the SDE user to reference ST_Geometry objects are not exported by the Oracle export utility.

Solution or Workaround

After importing the SDE schema into a new Oracle instance, the SDE user must execute the following commands in SQL*Plus to create the public synonyms.

Without public synonyms, any SQL that references ST_Geometry objects must be fully qualified by adding the SDE owner to the syntax; for example, sde.st_geometry.

To create the public synonyms, connect to the Oracle instance using SQL*Plus as the SDE user, and copy and paste the following list into SQL*Plus.

Code:
CREATE PUBLIC SYNONYM USER_ST_GEOMETRY_COLUMNS FOR USER_ST_GEOMETRY_COLUMNS_V;
CREATE PUBLIC SYNONYM ALL_ST_GEOMETRY_COLUMNS FOR ALL_ST_GEOMETRY_COLUMNS_V;
CREATE PUBLIC SYNONYM USER_ST_GEOM_INDEX FOR USER_ST_GEOM_INDEX_V;
CREATE PUBLIC SYNONYM ST_Geometry FOR ST_Geometry;
CREATE PUBLIC SYNONYM ST_Point FOR ST_Point;
CREATE PUBLIC SYNONYM ST_Curve FOR ST_Curve;
CREATE PUBLIC SYNONYM ST_Surface FOR ST_Surface;
CREATE PUBLIC SYNONYM ST_GeomCollection FOR ST_GeomCollection;
CREATE PUBLIC SYNONYM ST_Linestring FOR ST_Linestring;
CREATE PUBLIC SYNONYM ST_Polygon FOR ST_Polygon;
CREATE PUBLIC SYNONYM ST_MultiPoint FOR ST_MultiPoint;
CREATE PUBLIC SYNONYM ST_MultiLinestring FOR ST_MultiLinestring;
CREATE PUBLIC SYNONYM ST_MultiPolygon FOR ST_MultiPolygon;
CREATE PUBLIC SYNONYM ST_MultiCurve FOR ST_MultiCurve;
CREATE PUBLIC SYNONYM ST_MultiSurface FOR ST_MultiSurface;
CREATE PUBLIC SYNONYM ST_GeomFromText FOR ST_GeomFromText;
CREATE PUBLIC SYNONYM ST_PointFromText FOR ST_PointFromText;
CREATE PUBLIC SYNONYM ST_LineFromText FOR ST_LineFromText;
CREATE PUBLIC SYNONYM ST_PolyFromText FOR ST_PolyFromText;
CREATE PUBLIC SYNONYM ST_MPointFromtext FOR ST_MPointFromtext;
CREATE PUBLIC SYNONYM ST_MLineFromText FOR ST_MLineFromText;
CREATE PUBLIC SYNONYM ST_MPolyFromText FOR ST_MPolyFromText;
CREATE PUBLIC SYNONYM ST_Contains FOR ST_Contains;
CREATE PUBLIC SYNONYM ST_Within FOR ST_Within;
CREATE PUBLIC SYNONYM ST_Intersects FOR ST_Intersects;
CREATE PUBLIC SYNONYM ST_Overlaps FOR ST_Overlaps;
CREATE PUBLIC SYNONYM ST_Touches FOR ST_Touches;
CREATE PUBLIC SYNONYM ST_Crosses FOR ST_Crosses;
CREATE PUBLIC SYNONYM ST_Orderingequals FOR ST_Orderingequals;
CREATE PUBLIC SYNONYM ST_Equals FOR ST_Equals;
CREATE PUBLIC SYNONYM ST_Disjoint FOR ST_Disjoint;
CREATE PUBLIC SYNONYM ST_AsText FOR ST_AsText;
CREATE PUBLIC SYNONYM ST_AsBinary FOR ST_AsBinary;
CREATE PUBLIC SYNONYM ST_GeomFromWkb FOR ST_GeomFromWkb;
CREATE PUBLIC SYNONYM ST_PointFromWkb FOR ST_PointFromWkb;
CREATE PUBLIC SYNONYM ST_LineFromWkb FOR ST_LineFromWkb;
CREATE PUBLIC SYNONYM ST_PolyFromWkb FOR ST_PolyFromWkb;
CREATE PUBLIC SYNONYM ST_MPointFromWkb FOR ST_MPointFromWkb;
CREATE PUBLIC SYNONYM ST_MLineFromWkb FOR ST_MLineFromWkb;
CREATE PUBLIC SYNONYM ST_MPolyFromWkb FOR ST_MPolyFromWkb;
CREATE PUBLIC SYNONYM ST_Boundary FOR ST_Boundary;
CREATE PUBLIC SYNONYM ST_CoordDim FOR ST_CoordDim;
CREATE PUBLIC SYNONYM ST_Dimension FOR ST_Dimension;
CREATE PUBLIC SYNONYM ST_Envelope FOR ST_Envelope;
CREATE PUBLIC SYNONYM ST_Is3D FOR ST_Is3D;
CREATE PUBLIC SYNONYM ST_IsMeasured FOR ST_IsMeasured;
CREATE PUBLIC SYNONYM ST_IsClosed FOR ST_IsClosed;
CREATE PUBLIC SYNONYM ST_IsEmpty FOR ST_IsEmpty;
CREATE PUBLIC SYNONYM ST_IsRing FOR ST_IsRing;
CREATE PUBLIC SYNONYM ST_IsSimple FOR ST_IsSimple;
CREATE PUBLIC SYNONYM ST_Area FOR ST_Area;
CREATE PUBLIC SYNONYM ST_Buffer FOR ST_Buffer;
CREATE PUBLIC SYNONYM ST_Relate FOR ST_Relate;
CREATE PUBLIC SYNONYM ST_Centroid FOR ST_Centroid;
CREATE PUBLIC SYNONYM ST_ConvexHull FOR ST_ConvexHull;
CREATE PUBLIC SYNONYM ST_StartPoint FOR ST_StartPoint;
CREATE PUBLIC SYNONYM ST_EndPoint FOR ST_EndPoint;
CREATE PUBLIC SYNONYM ST_PointOnSurface FOR ST_PointOnSurface;
CREATE PUBLIC SYNONYM ST_ExteriorRing FOR ST_ExteriorRing;
CREATE PUBLIC SYNONYM ST_InteriorRingN FOR ST_InteriorRingN;
CREATE PUBLIC SYNONYM ST_NumInteriorRing FOR ST_NumInteriorRing;
CREATE PUBLIC SYNONYM ST_NumGeometries FOR ST_NumGeometries;
CREATE PUBLIC SYNONYM ST_GeometryN FOR ST_GeometryN;
CREATE PUBLIC SYNONYM ST_GeometryType FOR ST_GeometryType;
CREATE PUBLIC SYNONYM ST_Difference FOR ST_Difference;
CREATE PUBLIC SYNONYM ST_Union FOR ST_Union;
CREATE PUBLIC SYNONYM ST_SymmetricDiff FOR ST_SymmetricDiff;
CREATE PUBLIC SYNONYM ST_PointN FOR ST_PointN;
CREATE PUBLIC SYNONYM ST_Intersection FOR ST_Intersection;
CREATE PUBLIC SYNONYM ST_Transform FOR ST_Transform;
CREATE PUBLIC SYNONYM ST_Entity FOR ST_Entity;
CREATE PUBLIC SYNONYM ST_Numpoints FOR ST_Numpoints;
CREATE PUBLIC SYNONYM ST_MinX FOR ST_MinX;
CREATE PUBLIC SYNONYM ST_MaxX FOR ST_MaxX;
CREATE PUBLIC SYNONYM ST_MinY FOR ST_MinY;
CREATE PUBLIC SYNONYM ST_MaxY FOR ST_MaxY;
CREATE PUBLIC SYNONYM ST_MinZ FOR ST_MinZ;
CREATE PUBLIC SYNONYM ST_MaxZ FOR ST_MaxZ;
CREATE PUBLIC SYNONYM ST_MinM FOR ST_MinM;
CREATE PUBLIC SYNONYM ST_MaxM FOR ST_MaxM;
CREATE PUBLIC SYNONYM ST_Length FOR ST_Length;
CREATE PUBLIC SYNONYM ST_Srid FOR ST_Srid;
CREATE PUBLIC SYNONYM ST_X FOR ST_X;
CREATE PUBLIC SYNONYM ST_Y FOR ST_Y;
CREATE PUBLIC SYNONYM ST_Z FOR ST_Z;
CREATE PUBLIC SYNONYM ST_M FOR ST_M;
CREATE PUBLIC SYNONYM ST_Distance FOR ST_Distance;
CREATE PUBLIC SYNONYM ST_EnvIntersects FOR ST_EnvIntersects;