topic Trouble with ST_GEOMETRY in Data Management Questions

Trouble with ST_GEOMETRY

AdamZiegler — Wed, 27 Feb 2013 20:04:32 GMT

Greetings all,

I have run into a wall and am in need of some direction. I recently tried to create a new feature class using ST_GEOMETRY and I cannot get the feature class to draw in ArcMap. I have the same issue when trying to preview the feature class in ArcCatalog.

The selected object failed to draw: ST_GEOM_TEST: Underlying DBMS error [ORA-00942: table or view does not exist][APPDEV1.ST_GEOM_TEST]

So, I have an Oracle 11g database with SDE 10.0 installed. Full disclosure on the upgrade to 10.0 from 9.3.1 is that we did not have the proper set up for ST_GEOMETRY on our database server box. We do not use SDE service anymore and were not planning to use ST_GEOMETRY. I have since updated the SDE libraries to know where to find the ST_GEOMETRY and RASTER .so binaries. We are running IBM AIX 6.1. There were some invalid objects that recompiled to valid after I fixed the SDE libraries. I also re-configured the listener per the instructions and all of that seems to be working properly.

I can use sde -o describe and describe_long on the table. The long description is below (fields between OBJECTID and SHAPE omitted for space preservation).

ArcSDE 10.0 for Oracle11g Build 2063 Tue May 22 14:45:23 2012
Attribute   Administration Utility
-----------------------------------------------------

----------------------------------------------------------------
Column Owner           : APPDEV1
Column Table           : ST_GEOM_TEST
Column Name            : OBJECTID
Row ID Column Type     : SDE Maintained
SDE Column Type        : SE_INT32
Column Size            : 10
Decimal Digits         : 0
Null Allowed?          : False

----------------------------------------------------------------
Column Owner           : APPDEV1
Column Table           : ST_GEOM_TEST
Column Name            : SHAPE
SDE Column Type        : SE_SHAPE
Column Size            : 0
Decimal Digits         : 0
Null Allowed?          : True
Layer ID               : 1167

Anybody have any ideas on where I can look or troubleshoot next? Any and all insights/ideas are welcome and let me know if more information is needed.

Thanks!
Adam Z

Re: Trouble with ST_GEOMETRY

VinceAngelo — Thu, 28 Feb 2013 10:56:58 GMT

I had a client site where a script was run to "secure" the database, and it wound up
removing all sorts of necessary permissions from the ST_GEOMETRY packages,
which generated spurious "does not exist" errors ("SDE.function" was interpreted
as "SDE.table", so the error wasn't about the package). I had to exercise all the
ST_* methods to demonstrate the issue, then used an operational instance to
determine which EXECUTEs had been removed from PUBLIC. Tech Support could
help you do the same.

- V

Re: Trouble with ST_GEOMETRY

AdamZiegler — Thu, 28 Feb 2013 12:04:55 GMT

Great, thanks for the insight Vince. I'll work through it and re-post any findings/solutions.

Adam Z

Re: Trouble with ST_GEOMETRY

AdamZiegler — Fri, 01 Mar 2013 14:35:13 GMT

Okay, I am back in ST_GEOMETRY business. The problem had a few causes.

The database and database server were not properly set up to use ST_GEOMETRY (see help)

Privileges were not granted to PUBLIC on all of the ST_% objects - EXECUTE and some SELECT, UPDATE, INSERT, DELETE

PUBLIC Synonyms were not created on all the necessary ST_% objects

To get up and running again:

Configured the database and database server correctly (see help)

Created the proper libraries pointing to the ST_GEOMETRY binaries

Added entries in the database server tnsnames.ora and listener.ora

Recompiled the invalid ST objects in the SDE schema

Applied the proper privileges to the ST_GEOMETRY objects

I was fortunate enough to get my hands on the grants of a fresh proper 10.0 database - thanks Jake

Using that I compared the grants to what my DB had

Here's the select statement to get the grants:

select grantee, owner, grantor, privilege, table_name from dba_tab_privs where owner='SDE' and table_name LIKE 'ST_%' order by table_name;

The grants that I was missing were added to a SQL script that I ran against the DB

[/INDENT]

Create the PUBLIC synonyms still needed

[INDENT]

Jake hooked me up again with a nice list of the synonyms from a good DB

That was compared to my DB

I created a sql script to create the synonyms

Here are the select statements to retrieve the necessary synonyms

select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'ST_%';

select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'USER_ST_GEOM%';

select synonym_name from all_synonyms where owner = 'PUBLIC' and synonym_name LIKE 'ALL_ST_GEOM%';

[/INDENT]

[/INDENT]

So, thank you to Vince for pointing me in the direction of my problem and thank you to Jake for getting me what a correct DB should be. I've attached the output of what the proper DB privileges and synonyms should be.

Thanks,
Adam Z