ORA-00920 errors in ArcGIS Server log files

[HalilSiddique]

Hi,

I have just had a quick look at our ArcGIS Server log files and seem to have found some errors that relate to getting the data for a particular map service.

SEVERE Sep 12, 2013 10:14:21 PM There is an error during the draw Licences (1.) Underlying DBMS error [ORA-00920: invalid relational operator ::SELECT /*+ LEADING INDEX(S_ S1360_IX1) INDEX(SHAPE F1360_UK1) INDEX(LICENCES A1360_IX1) */ SHAPE, IMPACT, OBJECTID ,S_,(2.) eminx,S_,(3.) eminy,S_,(4.) emaxx,S_,(5.) emaxy ,SHAPE,(6.) fid,SHAPE,(7.) numofpts,SHAPE,(8.) entity,SHAPE,(9.) points,SHAPE,(10.) rowid FROM (SELECT /*+ INDEX(SP_ S1360_IX1) */ DISTINCT sp_fid, eminx, eminy, emaxx, emaxy FROM SY_DES_CHYMP_OVERNIGHT,(11.) S1360 SP_ WHERE SP_,(12.) gx >= :1 AND SP_,(13.) gx <= :2 AND SP_,(14.) gy >= :3 AND SP_,(15.) gy <= :4 AND SP_,(16.) eminx <= :5 AND SP_,(17.) eminy <= :6 AND SP_,(18.) emaxx >= :7 AND SP_,(19.) emaxy >= :8) S_ , SY_DES_CHYMP_OVERNIGHT,(20.) LICENCES , SY_DES_CHYMP_OVERNIGHT,(21.) F1360 SHAPE WHERE S_,(22.) sp_fid = SHAPE,(23.) fid AND S_,(24.) sp_fid = SY_DES_CHYMP_OVERNIGHT,(25.) LICENCES,(26.) SHAPE AND (( false ))] [SY_DES_CHYMP_OVERNIGHT,(27.) Licences]. Roadworks2.MapServer VMGISAPP03.CORPOFLONDON.GOV.UK 103800  null 5432 12664 DrawGraphicCursor.NextGraphic

and also

WARNING Sep 13, 2013 12:01:56 AM The Layer:'Precautionary Road Gritting' in Map:'Layers' is invalid. The base table definition string "SY_DES_CLEANSING.Mech_Gritting_CWays_Pri1" is invalid. DBMS table not found [ORA-00942: table or view does not exist ] [SY_DES_CLEANSING.Mech_Gritting_CWays_Pri1][STATE_ID = 290768]. COLGIS_Road_Gritting.MapServer VMGISAPP03.CORPOFLONDON.GOV.UK 10801   17276 9824 MapServer.Connect

Has anyone else had similar errors or have any suggestions on what can be done to fix these?

Just a point on the second code - the layer does exist which is why I'm confused.

Thanks

Halil

[EmadAl-Mousa]

what ArcSDE version are you using ?

did you try the command:

sdelayer -o register -l <table,column> -e <entity_mask> -t <storage_type>
{[-C <row_id_column>[,{SDE|USER}[,<min_ID>]]]} [Spatial_Index]
[-E <{empty | xmin,ymin,xmax,ymax}>] [{-R <SRID> | [Spatial_Ref_Opts]}]
[-S <layer_description_str>] [-k <config_keyword>]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-q]


then try to restart your ArcGIS server and check the logs again.

ArcSDE Command reference: http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/admincmdref.htm

Regards,

[WilliamCraft]

Open the map document (i.e., MXD) for this particular map service and check to see if the layer referenced in the error messages shows a red exclamation mark.  If so, then you have a broken data source that needs to be repaired for the map service to stop throwing errors.   It looks like the underlying data source for that layer in the map document might be the SY_DES_CLEANSING.Mech_Gritting_CWays_Pri1 table within Oracle.  Which user are you using in your ArcSDE connection file for the map document's layer connections?  Does that user have permission to see the base table AND the A, D, S and F tables for that particular object class?  If the user does have the right permissions on all 5 tables and/or you are connecting as the schema owner (i.e., SY_DES_CLEANSING), then I'd be curious to know if you are using Oracle Spatial (i.e., SDO geometry) based on the error content.  I have heard of a number of issues with successfully getting layers to register with ArcSDE.  I know there are many other individuals on these forums with much more Oracle Spatial knowledge than myself.  🙂