Personally, I find the need to ensure correct Spatial Metadata for geometry tables in Oracle to be an annoying stumbling block that frequently catches me out when creating new tables that contain spatial data.
But, of course, Spatial Metadata is a necessity for every view and table that contains geometry columns. It defines the coordinate system (SRID), tolerance and bounds for the data and allows Oracle to successfully validate and build Spatial Indexes on the data.
In practice, however, with a large number of spatially enabled tables in an Oracle schema, checking Spatial Metadata for each table - one by one - is a laborious, time-consuming process. So, I set about trying to automate the checking process.
Along the way I found that there are a number of items in a normal Oracle database that one would want to exclude from this check – things like Recycle Bin and Log tables.
What I came up with is the query below.
|
select c.owner,
case
when exists (select table_name from dba_tables where owner=c.owner and table_name=c.table_name) then 'Table'
when exists (select view_name from dba_views where owner=c.owner and view_name=c.table_name) then 'View'
else 'unknown!'
end as "Type",
c.table_name as "Name",
c.column_name as "Geometry Column"
from dba_tab_columns c
where c.owner in ('SCHEMA_ONE', 'ANOTHER_SCHEMA')
and c.data_type='SDO_GEOMETRY'
and not exists (select table_name from ALL_SDO_GEOM_METADATA where table_name=c.table_name and column_name=c.column_name)
and c.table_name not like 'BIN$%' -- ignore recycle bin
and c.table_name not like 'DR$%' -- ignore Oracle Text tables
and c.table_name not like 'EUL%' -- ignore Discoverer End User Layer (EUL) tables
and c.table_name not like '%PLAN_TABLE' -- ignore explain plan tables and Toad explain plan table
and c.table_name not like 'MLOG$%' -- ignore materialized view log tables
and c.table_name not like 'RUPD$%' -- ignore materialized view log table
order by c.owner,
case
when exists (select table_name from dba_tables where owner=c.owner and table_name=c.table_name) then 'Table'
when exists (select view_name from dba_views where owner=c.owner and view_name=c.table_name) then 'View'
else 'unknown!'
end,
c.table_name
|
Before running this query, change the list of schemas ('SCHEMA_ONE', 'ANOTHER_SCHEMA' in the query above) to include the schemas you want to check in your database.
The output should be something like:
|
OWNER Type Name Geometry Column
------------------------------ -------- ------------------------------ ------------------------------
SCHEMA_ONE Table MAPPING_TABLE GEOM
SCHEMA_ONE Table RSP_ASSETS_COMBINED GEOM
SCHEMA_ONE Table RSP_ASSET_AUDIT GEOM
SCHEMA_ONE Table RSP_FLUSHING_LABEL GEOM
SCHEMA_ONE View GIS_CON_DIFF_ONE GEOM
SCHEMA_ONE View GIS_CON_DIFF_TWO GEOM
6 rows selected
|
This should make the job of keeping your Oracle spatial database running smoothly a little bit easier.
Subscribe
Comments