Spatial in Oracle database are columns with SDO_GEOMETRY data type. SDO_GEOMETRY is native Oracle data type which stores an entire geometry,

The query below lists:

(A) all columns with spatial data types accessible to the current user in Oracle database

(B) all columns with spatial data types in Oracle database

Query

A. All columns with spatial data types accessible to the current user

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where col.data_type = 'SDO_GEOMETRY'
      and col.owner not in 
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

B. All columns with spatial data types

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where col.data_type = 'SDO_GEOMETRY'
      and col.owner not in 
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

Columns

• schema_name - name of the schema
• table - name of the table
• column_id - column position in table
• column_name - name of the column
• data_type - type of spatial data

Rows

• One row represents one column with a spatial data type
• Scope of rows: all columns containing spatial data types in the database
• Ordered by schema name, table name and column position in table

Sample results