Find all spatial data columns in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-04

Table of Contents:


    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

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept