topic Re: Issues with spatial index and featuer display problem. in Data Management Questions

Issues with spatial index and featuer display problem.

KiranKandula — Thu, 17 Mar 2011 16:58:39 GMT

Problem Description:

I have migrated from ArcSDE 9.2 SP4 (SDELOB) data to ArcSDE 9.2 SP6 (ST_GEOMETRY) which is successful but now when I do the following steps having spatial index issues and featuer display problem.

1.User is exporting oracle DUMP of schemas like SDE,Process(ArcFM) and User schemas from production and importing to Dev server and then upgrading the SDE.
2.User can see all the features class but when zooming the featue class the features disappears with blank.
while recalculating the spatial index the following error message is thrown:
---------------------------
Error
---------------------------
Failed to add spatial index.
Underlying DBMS error[Error executing PL/SQL Block db_stgeom_create_index::ORA-29882: insufficient privileges to execute indextype]
---------------------------
OK
---------------------------
3. SDE and all data users are having proper permissions as per ESRI document.

4.while describing the feature class noticed that " layer is I/O Mode : LOAD and Spatial Index.: not exist"

C:\Documents and Settings\Administrator>sdelayer -o describe_long -l ehvstation,shape -i 5151 -s devserver -D gisdb -u relgdb -p pass
ArcSDE 9.2 for Oracle10g Build 1271 Tue Jun 24 07:47:53 2008
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: RELGDB
Table Name ...........: EHVSTATION
Spatial Column .......: SHAPE
Layer Id .............: 35
SRID .................: 2
Minimum Shape Id .....: 1
Offset ...............:
falsex: -38836199.998860
falsey: -24155999.999013
System Units .........:       9817.068091
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.003259629016
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=160,FULL
exist:        No
array form:   160,0,0
Layer Envelope .......:
minx: -1005607.63572,        miny:   2156884.12752
maxx:   -990204.08768,        maxy:   2258990.34402
Entities .............: nac+
Layer Type ...........: Extended SQL Type
Creation Date ........: 03/12/11 13:55:29
I/O Mode .............: LOAD
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: PROJCS["Reliance_LCC",GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMET
ER["Central_Meridian",82.5],PARAMETER["Standard_Parallel_1",11.16],PARAMETER["Standard_Parallel_2",31.84],PARAMETER["Scale_Factor",1.0],PARAMETER["Latitude_Of_Origin",0.0],UNIT["Meter",1.0]]
Layer Configuration ..: REL_ELECTRIC_DATA

System configration:

1.ArcGIS 9.2 SP6 on Windows XP, ArcSDE 9.2 sp6, ArcFM 9.2.1 with oracle 10.2.0.3 on windows 2003 R2 SP2

Have a great day.

Thanks
Kiran

Re: Issues with spatial index and featuer display problem.

ForrestJones — Mon, 21 Mar 2011 23:27:06 GMT

Hi Kiran,

Does the oracle "sde" user have "CREATE INDEXTYPE" permissions?

Thanks,

Re: Issues with spatial index and featuer display problem.

YogeshShah — Mon, 22 Aug 2011 18:49:11 GMT

Hi Kiran
Any luck with resolving this issue? If Yes, what is solution?
Thanks
Yogesh

Re: Issues with spatial index and featuer display problem.

anthonysanchez — Thu, 22 Dec 2011 12:00:53 GMT

Hi,

we had the same problem with ArcSDE 10.0 SP3 after Oracle (11.2) export/import. Granting execute privileges to sde.st_spatial_index fixed the problem. I don't know how the privilege got lost, though.

Martin

Hi folks,
Ive come across the before.
When you install the sde repository initially, many grants are made against sde objects to PUBLIC.
During a data pump schema level import, these public grants are not made and I don't know why. My solution was to scrape all the public grants and synonym ddl for sde objects out of a "virgin" sde schema and save them into a script. I would then run the script after every data pump import of the sde schema to restore all the public grants and synonyms.
Hope this helps.

Anthony

Re: Issues with spatial index and featuer display problem.

BarryGuidry — Tue, 04 Sep 2012 13:59:57 GMT

Hi Kiran,

Does the oracle "sde" user have "CREATE INDEXTYPE" permissions?

Thanks,

FJ, I am assuming this is a necessary permission in oracle, right?

Re: Issues with spatial index and featuer display problem.

VinceAngelo — Tue, 04 Sep 2012 15:48:37 GMT

FJ, I am assuming this is a necessary permission in oracle, right?

There's a number of required permissions at install; the documentation details them, but I
generally grant DBA for the duration of the post-install (install or upgrade). After that, the
SDE user doesn't need much more than CREATE SESSION.

- V

Re: Issues with spatial index and featuer display problem.

ZacharyStauber — Tue, 12 Mar 2013 18:06:51 GMT

I got this same error, and the suggestion above worked. As SDE schema owner:
GRANT EXECUTE ON sde.st_spatial_index TO <username>;
allowed <username> to register a table with Oracle SDE from the Solaris OS with the sdelayer command.
Thank you so much!