Issues with spatial index and featuer display problem.

[KiranKandula]

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

[ForrestJones]

Hi Kiran,

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

Thanks,

[YogeshShah]

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

[anthonysanchez]

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

[BarryGuidry]

Hi Kiran,

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

Thanks,

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

[VinceAngelo]

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

[ZacharyStauber]

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!