corrupted oraclespatial/arcsde layer

[mollythompson]

I have oracle spatial table myschema.mytable1 on oracle instance - instance1
I have other attribute table mytable2 on oracle instance - instance2.
There is NO link between these two tables...

i have registered mytable1 as sdelyaer so that i can see it in arcgis.

I have a small tool i developed using VB.net and in that tool, i am have some code like this -
        Dim iselset As ISelectionSet
        Dim myfeatcur As IFeatureCursor
        Dim iqryfil As IQueryFilter
        iqryfil = New QueryFilter()
        iqryfil.WhereClause = " objectid >0 "

        myfeatcur = fetlayer.Search(iqryfil, False) 
        'fetlayer is the sde-layer i created using sdelayer command of mytable1

Now when i loop through that myfeatcur, for some features, I get wiered error message - saying that  mytable2 does not have OID field!!!! When there is no link between mytable1 and mytable2.

Everthing was working fine before.. Since last few days i am getting this error.. Does it mean my data in that layer is corrupted? I can still easily see that layer in arcmap and  arccatalog.
Is there any way to check this layer's data?

Thanks a lot...

[VinceAngelo]

What version of Oracle are you using (A.B.C.D notation)?

What version of ArcSDE are you using (including service pack)? What version of
ArcGIS are you using (w/ SP)?

Please provide the 'sdetable -o describe', 'sdetable -o describe_reg', and 'sdelayer -o
describe_long' output for table1, plus the USER_SDO_GEOM_METADATA content
for that table.

Objectids should always be positive non-zero values, so your where clause shouldn't do
anything. What does "SELECT COUNT(*) FROM table1 WHERE objectid <= 0" return?
How about "SELECT COUNT(*) FROM table1" and "SELECT COUNT(DISTINCT objectid)
FROM table1"?

If anything, it's not the layer which has been corrupted, but the instance. Have you
used DROP TABLE on any table registered with ArcSDE, or any layer registered with
the geodatabase? Has the 'table2' table ever existed in instance1?

- V

[mollythompson]

Oracle Database Version :
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production.

sdelayer -o describe_long -l mytable1,geom -i sde:Oracle10g -s myserver1  -u myuserid  -p mypwd@myinstance1

ArcSDE 9.2  for Oracle10g Build 1271 Tue Jun 24 07:47:53  2008
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: MySchema1
Table Name ...........: MYTABLE1
Spatial Column .......: GEOM
Layer Id .............: 290
SRID .................: 3
Minimum Shape Id .....: 1
Offset ...............:
  falsex:    1151500.000000
  falsey:     316000.000000
System Units .........:       1130.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.0
Spatial Index ........:
  parameter:    SPIDX_RTREE
  exist:        Yes
  array form:   -2,0,0
Layer Envelope .......:
  minx:   1151500.00000,        miny:    316000.00000
  maxx:   1417700.00000,        maxy:    632000.00000
Entities .............: p
Layer Type ...........: In-Line Spatial Type
Creation Date ........: 06/04/10 16:13:24
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: PROJCS["Maryland 1900 (1983, US Survey feet)",GEOGCS["NA
D 83 (Continental US)",DATUM["NAD 83 (Continental US)",SPHEROID["GRS 80",6378137
.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Decimal Degree",0.0174532925199
433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",1312333.33
33],PARAMETER["Central_Meridian",-77.0],PARAMETER["Standard_Parallel_1",38.3],PA
RAMETER["Standard_Parallel_2",39.45],PARAMETER["Latitude_Of_Origin",37.666667],U
NIT["U.S. Foot",0.3048006096012]]

Layer Configuration ..: SDO_GEOMETRY



sdemon -o info -I config -i sde:Oracle10g -s cob-oramd-01

ArcSDE I/O Manager Configuration Parameters at Sun Jun 06 09:22:18 2010
-------------------------------------------------------------------------
ArcSDE Version                    9.2
ArcSDE Server Build                for Oracle10g Build 1271 Tue Jun 24 07:47:53
2008
Underlying DBMS                   Oracle
Root Path                         C:\arcgis\ArcSDE\ora10gexe

I DONOT KNOW EXACT SERVICE PACK OF ARCSDE, AS I CANNOT LOG INTO THAT LINUX BOX WHERE THIS IS INSTALLED.

SELECT COUNT(*) FROM table1 WHERE objectid <= 0
---Returns 0

SELECT COUNT(*) FROM table1" and "SELECT COUNT(DISTINCT objectid)
FROM table1
--Returns the same- 400000

Have you used DROP TABLE on any table registered with ArcSDE, or any layer registered with
the geodatabase?
>>> YES (I dropped few tables last week but it has nothing to do with this table,

Has the 'table2' table ever existed in instance1?
>>>No. 

Thanks a lot....

[VinceAngelo]

If you use SQL to DROP registered tables, your geodatabase registry could be corrupted,
which can produce cross-association when new layers are registered.  At this point, you
really ought to contact Tech Support, since they have the tools to diagnose and fix registry
issues.

- V