Get Spatial Table Information Oracle Spatial

Question

How can I get the tablename, Srid and GeometryType of all spatial tables in Oracle(I use 11g)?

In postgis, I can do something like

select f_table_name,srid,type from public.geometry_columns order by f_table_schema, f_table_name;

Does anything similar exist in Oracle?

Answer 1

The easy way is to select * from user_sdo_geom_metadata or select * from all_sdo_geom_metadata. This isn't as thorough as pecoanddeco's answer would provide but if your metadata is up to date it's pretty good.

Note that individual geometry rows can have different SRIDs from the metadata (although that will break spatial indexing). For geometry types your best bet is to look at the parameters on the spatial index, it's common (but not essential) to have a layer_gtype= parameter, eg layer_gtype=point.

Answer 2

I've not got a copy of Oracle running here at the moment but the ALL_TAB_COLUMNS table will give you a list of all columns and their related tables in the database, so you should be able to filter this to give you a list of the geometry tables.

See http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2091.htm

I'm not sure you can get a list of the SRID and geometry types per table though as I think this is set on the geometry object itself rather than on each table, i.e. each geometry column can contain a mixture of geometry types and SRIDs.

• vinayan: "each geometry column can contain a mixture of geometry types and SRIDs"..thats strange! why would they do that :(
• pecoanddeco: Actually, try the USER_SDO_GEOM_METADATA or ALL_SDO_GEOM_METADATA views. These contain spatial info for tables.