Oracle Spatial View Problems

[SSMIC3038]

Had a request from a client that spatial views would resolve, did some initial testing in Oracle and everything looked good.
Pretty straight forward really, used the 'sdetable -o create_view' command, proper syntax, included the objectid and shape field, no problems with the initial views that were created.

Now going to implement in a production format and the results are getting flakey, it is sporadically generating the spatial component.
In ArcCatalog sometimes it generates the spatial elements, sometimes it dosn't.
The attribute data is always created.
ArcCatalog see's it as a feature class, not a table, so it is seeing it as a spatial view
Regardless of what ArcCatalog can/can't see ArcMap will always display the spatial side, but it will not select all the data. The shape is there, the attributes are there but cannot select or identify some individuals.

Did a full database compress, got rid of all versions, etc. Problem is still there, anyone have any idea why this may be happening?
Oracle 9i, ArcSDE 9.2

[VinceAngelo]

What service pack do you have appied to both ArcSDE 9.2 and Oracle 9i?

What geometry storage type are you using in the view's source table?

Does the registered rowid column return unique row values for each query?

- V

[SSMIC3038]

ArcSDE 9.2 service pack 6
Oracle 9.2.0.8
SDE compressed binary

Not sure if I follow on the last question, but each view created is returning the same sequence of ObjectID.

[SSMIC3038]

One more point to mention, the script I'm running should create a view with all data from the parent feature but the end result is only producing a subset of the entire dataset.

[SSMIC3038]

When exporting the datasets out to PGDB and then back into SDE, all the same processes work without problems. The spatial views are made correctly and act as I would expect.

Now the bad news, this all pertains to a massive enterprise GIS, we run this thing centrally for SEVERAL municipalities, water and electric utiltity and a regional health unit.
This GIS was originally made on 8.0 (dev started on 8.0 pre-release actually) and has seen every product upgrade since 2000 up to our current 9.2 version.

[ThomasDunn]

Because your methods worked in testing, both using Oracle and ArcSDE, and because reloading the datasets (I assume you mean the tables underlying the views) I suspect that the production data has problems with their spatial indexes.  Reloading the data recreated the spatial indexes. 

The next time you encounter the problem, here are a couple of things to try.

1. Rebuild the spatial index on the underlying spatial table.  You can do this from ArcCatalog or with the command "sdelayer -o load_only_io" followed by "sdelayer -o normal_io". Then retry your query or draw.

2. Perform a SQL trace to capture the spatial query generated by ArcSDE.  Execute the SQL query against the database directly to see what results you get.  You may find that the SQL query returns the same incorrect results.  Whether it does or it doesn't, this will help determine where to look for the problem, in the database or in the software stack above it.

3. Get ESRI Technical Support involved. 

-- TAD

[SSMIC3038]

Thanks for the input:
1.  A bit odd here: rebuilding the spatial indexes on the feature caused all existing views based on that feature to work properly but any new views are still experiencing the old problems when they are created, rebuilding the spatial index only affects existing views - not new ones.
So we have a partial solution in that we can unversion the data > create view > rebuild index > version the data > proceed with normal GIS events.
Not a complete solution but it works for now.

2. Still have to do this.
3. ESRI tech support incident opened and on-going

Seeing as our database was started at ArcGIS 8.0, part of me wonders if the migration from low>high precision has anything to do with this?