How do I determine what's using Oracle Spatial?
We have an Oracle Enterprise Edition 10 installation and as its been explained to me by our DBAs, Oracle Enterprise installs include all extensions and you're simply licensed by what you use.
We've discovered we're using Oracle Spatial but we don't want to be. I can confirm for myself that its being used with this SQL:
select * from dba_feature_usage_statistics;
Unfortunately that's all I can find out. We have a large number of applications which use Spatial elements, but having asked all of our vendors they assure us their apps are using Oracle Locator (which is the free subset of Spatial).
So my question is simple - how do I discover exactly which app is using the Oracle Spatial extension? Alternately (brought to light by ik_zelf's answer), how do I prove I'm only using the Locator subset of Spatial.
Answer
Check the sdo metadata:
select * from mdsys.sdo_geom_metadata_table where sdo_owner not in ('MDSYS', 'OE')
when you dig a little deeper in the dba_feature_usage_statistics you will find this query as part of the determination of what is being used and what not. The schema's MDSYS and OE are not counted, even when they have sdo objects.