Are you really using the Oracle Database Option Spatial or is it really Oracle Locator

Question

We have run the Oracle Review LITE scripts and it shows we have the Oracle Database Option Spatial in-use so we need to buy additional licenses. How do we know this is correct and that it’s not the Oracle Option Locator which is free?

Answer

It is very difficult to be sure if it’s Oracle Spatial or the Oracle Locator Option installed and in-use from running Oracle scripts or the verified tools. You’ll need to get your DBAs to dig a little deeper. There are two methods for determining spatial usage:

1. DBA_FEATURE_USAGE
2. SDO_GEOM_METADATA

DBA_FEATURE_USAGE

Looking at DBA_FEATUREUSAGE_STATISTICS table you will see which databases are showing spatial usage based on “Spatial” showing as TRUE. There is no additional evidence in these databases beyond the SDO_GEOM_METADATA objects. As the existence of these objects only confirms Locator or Spatial usage it is hard to determine exactly which are really using Spatial. From 12c onwards additional feature info is available to clarify that it is indeed Spatial and not Locator. Prior to this I would argue that no evidence exists that Spatial rather than locator was used. 

SDO_GEOM_METADATA

This query is of limited benefit and really only highlights potential Spatial usage and should not be considered the only piece of evidence. We have used an additional rule which uses DBA_REGISTRY and looks for “Spatial” within that and ensures that it has a value of VALID before confirming Spatial usage. As an aside database prior to 9.2 you won’t have DBA_FEATURE Usage.

Resolution

If you can run mddins.sql on the database and the application still works then only locator was in use. However, up until 12c there is no additional evidence that demonstrates that Spatial rather than Locator is in use. If you remove Spatial using the mddins.sql script the DBA_REGISTRY will show as REMOVED. This should be sufficient evidence to show that only locator is in use. However, within DBA_REGISTRY the data of last modification will be shown and Oracle may take exception to this.

Reference

http://spatialdbadvisor.com/ is a blog dedicated to Oracle Spatial which may be of interest to DBA’s wanting to get more detail on this topic.