Using pure SQL, we can find the data type of a column using the
WHERE UPPER(OWNER) = 'THE_SCHEMA' AND UPPER(TABLE_NAME) = 'THE_TABLE'
One of the result columns is
DATA_TYPE. Note that there is a
USER_TAB_COLUMNS if you are connecting as the schema that holds the table of interest.
We could also use
But the result of that command cannot be queried any further.
We can also use
ALL_TAB_COLUMNS to try to discover what spatial columns we have:
SELECT * FROM ALL_TAB_COLUMNS WHERE DATA_TYPE = 'SDO_GEOMETRY';
ST_GEOMETRY can be easily added to the
WHERE clause. Based on Vince's comment, the following filter seems like a good place to start:
DATA_TYPE LIKE '%GEOM%' OR
DATA_TYPE LIKE '%SDE%'
Note that neither Oracle nor ArcGIS will be able to do much with the columns unless the
USER_SDO_GEOM_METADATA has an entry for each geometry column and a spatial index is added.