How to detect Spatial feature usage?

By using all_sdo_geom_metadata view.

column spatial format a10;
SELECT DECODE(COUNT(*), 0, 'No', 'Yes') Spatial
FROM
  ( SELECT 1 FROM all_sdo_geom_metadata WHERE rownum = 1
  );

Sample Output:-