Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

[JoëlHempenius3]

My organization is planning to update all Arcgis Software from 10.3.1 to 10.7. I'm currently testing the 10.7 products and I've run into an error with using a small number of tables from my Enterprise Geodatabase from ArcCatalog 10.7 and Arcgis Server 10.7

When I try to get the properties of this table in ArcCatalog, I get a message: Schema out of date, Retry as owner or sdeadmin [THEMA_BAG.VBO_ADRESSEN][STATEID = 90368]

From Arcgis Server I get:

WARNING May 20, 2019, 12:01:54 AM The Layer:'Verblijfsobjecten' in Map:'Layers' is invalid. The base table definition string "THEMA_BAG.VBO_ADRESSEN" is invalid. Schema is out of date, Retry as owner or sdeadmin [THEMA_BAG.VBO_ADRESSEN][STATE_ID = 90368]. Geodiensten/Externe_Data.MapServer 

Arcgis Server was installed with 10.3.1 and the mapservice was created using a 10.3.1 client, after installing and creating the mapservices, I upgraded to 10.7

When I run this code in Arcpy from ArcCatalog 10.7:
fields = arcpy.ListFields(r"E:\SDE\geodiensten_view@barp.sde\THEMA_BAG.VBO_ADRESSEN')
print (str(len(fields))), I get 0, so Arcpy can see the table, but it cannot get the fields.

Arcmap, ArcCatalog and Arcgis Server 10.3.1 can use the table without any error. 

My Enterprise geodatabase contains 1000+ Featureclass, most of them can be used with 10.7 without any problem, there are only around 10 tables which result in the same error and 0 arcpy fields. 

The user has only select on the table in both cases.

I use Oracle 12c 12.1.0.2.0

Running this code with ArcCatalog 10.3.1 gives:

>>> desc= arcpy.Describe('E:\SDE\geodiensten_view@barp.sde')

>>> desc.currentRelease

True

>>> 

Normally, when I get this error, I would use Schema owner to connect in ArcCatalog and hit the table to fix the Schema out of date, but I do this with both client and SDE at the same version.

In this case I'm afraid I might break something on the 10.3.1 side when I use the Schema owner on ArcCatalog 10.7 to connect to this table. The Enterprise Geodatabase contains 1TB+ of data, so restoring the database from backup after I break something is possible, but time consuming. 

So my question is: can I solve this Schema out of date with a 10.7 ArcCatalog and is it safe to do for the exiting 10.3.1 clients?

[PeterHanmore]

I have not asked our DBA to change any permissions up until now.  The vast majority of layers in the schema will draw just fine and they all have been granted the same Oracle role (it is a read only role) so I'm not sure that that is the issue but it's worth checking.
Do you recall what additional privileges you had to grant?

[MichaelVolz]

See if this thread provides any insight into the issue you are seeing:

https://community.esri.com/message/680816-desktop-client-issues-in-105-when-using-oracle-sde-databas...

[JoëlHempenius3]

Yes, 

My local support team asked me to create a query layer of this oracle table and in this process I could not use the ID field als objectid. So there was something wrong with the ID field. The problem was that the ID field was a NUMBER type and this should be NUMBER(38,0) in Oracle. After this was fixed, the error messages disappeared in 10.7

[MichaelVolz]

So when you created the query layer you added an additional field in Oracle that was defined as number (38,0)?  This step solved the original problem that you were facing of schema out of date?

[JoëlHempenius3]

No, I did not create the query layer, because it triggered to look into the data type of this column. A Oracle specialist created a new column with NUMBER (38,0), copied the ID column values into the new column and renamed both columns so the ID column had the NUMBER (38,0) and then I fixed the schema out of date when I connected to this table with schema owner.

[MichaelVolz]

So if new records get added to this database table will you need a script that will create these values in this new field in order to avoid this error in the future?

Have you tried to perform a subquery (query of a query) on this query layer and then try sorting the query layer?  This is an issue I have encountered, but it seems like the new field you created might avoid this scenario.  In our scenario we used WireShark to determine that the SQL statement being sent to the Oracle database was being truncated so sorting of a subquery was failing.

[MarcoBoeringa]

Do note that the OBJECTID field added by e.g. the Add Incrementing ID Field tool (that can be used against tables not registered with a geodatabase to create a unique ID field), will normally also create a database sequence on the same field, to auto-update the objectids whenever data is added to the same table. So simply copying existing data from another OBJECTID field to a newly created field, is not the same if you do not manually also add such a sequence to the new field.

[ReinholdSchnizer]

Hello,
I had a similar problem with Arc* Clients (Versions 10.6.1 and 10.2.1) and Geodatabase 10.2.1 in Oracle 12.2.
We too have 1000+ feature classes and 350 registered views, created mostly with the old sdetools.
A few of them make problems with "schema out of date...", opening the view as owner + OK or recreating the view and new registration didn't help.

At least for one view I found the reason:
There was a difference in string lengths between Oracle data dictionary and SDE Repository.
The reason was a column with a string concatenation which included an implicit date to string conversion.
The result differed between languages (we use german clients and english servers), therefore the stored column details in SDE were different to the column as seen in the client.
For the analysis I needed days, but the solution was simple:
I added an explicit format in the expression, so Oracle data dictionary and SDE repository are always consistent.

In my case I had to drop/recreate the view and register it with 10.6.1, since registering views in 10.2.1 with arcpy isn't possible.
see https://community.esri.com/thread/210919-unregistering-a-spatial-view
Until now I only tried it in my testing environment, I am also waiting for an answer from ESRI/Partner about using a newer ArcCatalog for administration of an older Geodatabase.

Here is my SQL to select registered tables/views with different character string lengths:

select * from (
select c.owner,c.table_name,c.column_name,c.sde_type,c.column_size,d.data_type,d.data_length
from dba_tab_columns d, sde.column_registry c
where c.owner=d.owner and c.table_name=d.table_name and c.column_name=d.column_name
)
where column_size <> data_length
and   data_type in ('VARCHAR2','NVARCHAR2')
and   owner <> 'SDE'
;