Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
