Hexagon Geospatial
MENU

Spatial Modeler

Discuss topics with other Hexagon Geospatial Product pioneers and experts to get the most out of our products.
Showing results for 
Search instead for 
Do you mean 
Reply
Occasional Contributor
Posts: 8
Registered: ‎07-27-2016
Accepted Solution

Create a new feature class - ORA-00942 on SDO_GEOM_METADATA_TABLE

Hi everyone,

 

 

When I try to create a new feature class (via GeoMEdia -> Feature Classes), GeoMedia tries to insert into SDO_GEOM_METADATA_TABLE:

 

 

GMDatabase:Smiley TonguerepareStatement - pSQL: 'insert into MDSYS.SDO_GEOM_METADATA_TABLE (...)


Database Error Number: 942
Database Error Message: ORA-00942: Tabelle oder View nicht vorhanden freshData()

 


Then it goes with MDSYS.USER_SDO_GEOM_METADATA

 

 

Is this "by design" ? Why are we trying to access SDO_GEOM_METADATA_TABLE instead of going directly to USER_view?

 

 

RGDS Cezary Zielinski

Highlighted
Staff
Posts: 156
Registered: ‎02-02-2016

Re: Create a new feature class - ORA-00942 on SDO_GEOM_METADATA_TABLE

[ Edited ]

Hi Cezary,

 

I suspect that you are able to create your feature class but see the ORA-00942 in logging only and are curious about the error in your log file.

 

As you are aware, Oracle has it's own MDSYS metadata that must be populated for spatial feature tables; GeoMedia also has it's own set of metadata stored in the GDOSYS schema.  When features are created by GeoMedia (Feature Class Definition for example), GeoMedia will populate both Oracle's and GeoMedia's metadata.    It is by design that GeoMedia will first try to populate the MDSYS metadata directly using the MDSYS.SDO_GEOM_METADTA_TABLE.  It then has fall back logic to try populating the MDSYS metadata via the USER_SDO_GEOM_METADATA. 

 

Find below an extract from the GeoMedia Oracle Object Model (GOOM) Package quick reference guide that may shed more light on this subject.

 

7. ORACLE METADATA – SETTING THE MBR AND TOLERANCE FOR GEOMETRIES


All columns of type SDO_GEOMETRY require entries in Oracle's spatial metadata table: SDO_GEOM_METADATA_TABLE which is stored in MDSYS. This table is used to store the spatial tolerance and spatial extents (minimum bounding rectangle – MBR) of each spatial column in the database. This information is used for indexing and analysis of the geometry data.


Oracle provides access to this metadata table via 2 views:

  • ALL_SDO_GEOM_METADATA - Lists metadata for all tables the user has privilege to see (SELECT). This view is read-only which means that a user must own the table in order to populate Oracle's metadata for that table.
  • USER_SDO_GEOM_METADATA – Lists metadata for the tables the user owns. This view is fully writable allowing users to insert metadata for tables in their schema.


Normally, applications update Oracle's metadata via USER_SDO_METADATA, which is writable, and access the metadata via ALL_SDO_GEOM_METADATA, which is read-only (as is its parent table). You can get around this restriction by granting insert privilege in MDSYS.SDO_GEOM_METADATA_TABLE to the user (or to PUBLIC) that requires the capability. This is not recommended but may be necessary in cases where you want a user to create tables in the master schema.


GeoMedia requires all feature classes (even those that are views) to have entries in the MDSYS.SDO_GEOM_METADATA_TABLE. During a connection, it will read from ALL_SDO_GEOM_METADATA to get the information it requires. When running a GeoMedia command that creates a table in Oracle, GeoMedia will first try to populate the MDSYS.SDO_GEOM_METADATA_TABLE and if that fails, it will next try to populate USER_SDO_GEOM_METADATA. If you are connected as a user that does not own the table you are creating and proper privileges have not been established, the process will fail. All columns of type SDO_GEOMETRY require Oracle metadata. This includes views (a GeoMedia requirement, not Oracle).

 

Occasional Contributor
Posts: 8
Registered: ‎07-27-2016

Re: Create a new feature class - ORA-00942 on SDO_GEOM_METADATA_TABLE

Hi,

 

Thank you. It helps to clear it out.

 

Why can't Intergraph put this kind of info into official documents?

 

RGDS  Cezary Zielinski

Do you need immediate support?
If you encounter a critical issue and need immediate assistance please submit a Service Request through our Support Portal.