HOW TO

Discover all tables and views in an Oracle instance which have a st_geometry attribute

Last Published: April 25, 2020

Summary

Instructions provided describe how to discover all tables or views in an Oracle instance which have a st_geometry attribute with the given SQL syntax.

It may become necessary to know which schemas have tables or views containing a st_geometry attribute in the case where the SDE schema must be dropped. If the SDE schema is removed prior to removing the st_geometry attribute from existing tables, once the SDE schema has been dropped (thus the removal of the st_geometry type), the tables may no longer be accessible.

Procedure

To list all tables or views in an Oracle instance that have an st_geometry attribute execute the following SQL statement in SQL*Plus as a user who has access to the view DBA_TAB_COLUMNS.

Code:
SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE data_type_owner = 'SDE'
ORDER BY owner, table_name;

    Article ID:000010845

    Software:
    • Legacy Products

    Receive notifications and find solutions for new or common issues

    Get summarized answers and video solutions from our new AI chatbot.

    Download the Esri Support App

    Discover more on this topic

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options