Uninstalling Oracle Spatial

[Anonymous User]

Original User: nsridb

Over the last few years we have been using ArcSDE on an Oracle RDBMS to hold our spatial data. I am the DBA for this system and it was my understanding that we weren't using Oracle Spatial as we don't have a licence for it and thus we have always done all our Spatial work using ArcSDE clients.

It now transpires that at some point in the past someone installed Oracle Spatial on the server and it appears that the default dbtune keyword uses the SDO_GEOMETRY storage type. It is difficult to determine exactly which of our tables/maps are affected or not.

We need to uninstall the Oracle Spatial from our server. Can anyone advice me what would be the implications of this? Is there any easy way of converting all the SDO_CEOMETRYS to ST_GEOMETRY? or will I have to export all the maps, change the dbtune geometry storage instructions and reload all the maps and then uninstall Oracle Spatial once we are sure none of the maps are using the SDO_GEOMETRY structure.

Any help gratefully received.

Caroline

[Anonymous User]

Original User: vangelo

There's a big difference between Oracle Spatial and SDO_GEOMETRY.  Oracle Spatial uses
SDO_GEOMETRY, but SDO_GEOMETRY is not part of Spatial. The SDO type is installed with
Intermedia, which is part of the base 11g install.  I never install Oracle Spatial (which is more
of an ArcGIS Server competitor) and can still use SDO_GEOMETRY when needed.

There are migration tools to convert SDO_GEOMETRY into ST_GEOMETRY, but I haven't used
them, and I'm not much of a "migrate" fan, preferring to reload (and optimize while I'm at it).

What you should probably do is create a new instance, with Spatial and whatever you have
loaded, install a representitive sample (or the whole database, if you have the space) and
run through the Spatial uninstall procedure, to see if it damages your ArcGIS/ArcSDE install
(I doubt it will).  You can even use the duplication process to verify your backup procedures,
because you'll want to be sure they're up to snuff before making changes like type migration
to the production instance.

- V

[Anonymous User]

Original User: nsridb

Thanks Vangelo for that useful information, we will attempt a test uninstall to see if it does cause problems as you suggest. Before I read your answer I did try to use sdelayer -o migrate on one of the tables, although it seemed to work OK, and the table is now shown to have ST_GEOMETRY, I can no longer display the map in ArcCatalog as it now throws an "insufficient privilege" error. So if we can get by without migrating the geometry_storage I'll be much happier.

Caroline

[VinceAngelo]

If you migrated as the owner, and you're getting privilege issues as the owner, then it's
likely that your ArcSDE installation is somehow corrupt.  Again, building a new instance
can be of help, though this time you just need to install ArcSDE in it, then look at the
permissions granted on the objects owned by SDE, and compare them with the permissions
granted in your production instance.  You should be working with Tech Support to address
this issue.

- V

[Anonymous User]

Original User: nsridb

OK we created a duplicate server and checked out the mdsys.all_geometry_columns table and it was empty, so I figured this was evidence that we had never actually used Oracle spatial and created any table using it. I therefore thought we would be safe uninstalling Oracle spatial.

Unfortunately now we have uninstalled Oracle Spatial it has removed the SDO_GEOMETRY type definition which was in the MDSYS schema. The ArcSDE layers which had spatial columns defined as SDO_GEOMETRY were using the mdsys.sdo_geometry type and therefore have stopped working. I'm getting a bad feeling about this and beginning to think we are going to end up haveing to export all our maps and reimport them using the ST_GEOMETRY. Unfortunately there are a lot of maps and these are live systems.

It's all a bit of a headache.

I'm not sure we have Intermedia installed, if we install it will it replace the MDSYS.SDO_GEOMETRY type definition? and if we uninstall Oracle Spatial after that will it leave the sdo_geometry type in there as it was installed with Intermedia as well?

[VinceAngelo]

I know that the instance will be irrevocably damaged if  you drop the SDE user while
ST_GEOMETRY columns still exist, so trashing the MDSYS would likely do the same,
I have no idea how you can have SDO_GEOMETRY layers without
USER_SDO_GEOM_METADATA rows.

You should probably review what the removal script is doing, and create a clone that
isn't so all-encompassing.  SDO_GEOMETRY is available on systems without Spatial,
so this should not be a big deal.

- V

[Anonymous User]

Original User: nsridb

Your right I was looking in the wrong tables. That's the problem with never having used Oracle Spatial, or at least thinking I hadn't.

When I check the ALL_SDO_GEOM_METADATA table there are loads of tables in there. All those that we had created in ArcSDE with the GEOMETRY_STORAGE flag set to SDO_GEOMETRY in DBTUNE in fact.

I'm on a steep learning curve here.

[VinceAngelo]

You hadn't used Spatial, but you hadn't used the MDSYS.ST_GEOMETRY wrapper
around SDO_GEOMETRY either. 

It certainly is a slippery slope.

- V

[Anonymous User]

Original User: nsridb

Quick further update. We have gone back to the idea that we need to migrate the storage from SDO_GEOMETRY to ST_GEOMETRY so I'm testing the sdelayer -o migrate command on a duplicate server. The first problem we were having was the privilege issue mentioned above. This turned out to be reasonable easy to fix with an extra command in the LISTENER.ora file which told oracle where the ST library file was, as it was the ST operations that it couldn't access. New problem is that the tables have been using the british national grid coordinate reference definition from the SDO_COORD_REF_SYS table (SRID=81989) while they should be using SRID=27700 in the ST_SPATIAL_REFERENCES table. I'm not sure how to switch the SRID's over.

The migration tool is throwing the error:
Error executing PL/SQL Block db_stgeom_create_index_by_schema
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20083: Parameter ST_SRID 81989 does not exist in ST_SPATIAL_REFERENCES table

Does anyone know how to resolve this or should I just add a new record to ST_SPATIAL_REFERENCES which is a duplicate of the SRID 27700 line but change the SRID to 81989?

[VinceAngelo]

"SRID" means very different things to Oracle and Esri.  For Oracle, it's only the coordinate
system, for which they have a fixed table of values.  To Esri, it is a true "spatial reference,"
which includes the constants for dimensional encoding in the ST_GEOMETRY byte stream;
since there are an infinite number of combinations, the spatial references table ID is just
a sequence-fed value for the unique sets of values used in the database instance.

The best way create an appropriate SDE.ST_GEOMETRY SRID is simply to copy a small table
from the base SDO_GEOMETRY to a ST_GEOMETRY layer.  Then use the SRID of that layer
(1? 2?) for the conversion process.  You can even delete the copied table, since SRIDs
aren't purged with DROP TABLE. 

You can save yourself some disk and improve ST_GEOMETRY performance by limiting the
resolution of the ST_GEOMETRY layer to a value appropriate to your data (the default XY
scale is likely to assert 1mm to 1/10mm scale [1000 to 10000], when you can probably
get away with 1/2cm to 1/5 cm scale [200 to 500 xyunits]).  If the conversion goes
smoothly, you might want to consider exporting and reimporting the tables, since they
are likely to have been badly fragmented by the migration process.  The performance
difference between the original SDO storarage and defragmented optimized ST_GEOMETRY
is likely to be measurable.

- V