When deleting an unused field, don't break map service

[Bud]

Enterprise 10.7.1, Oracle 18c EGDB

Scenario: I've deleted a field from a FC. The FC is used by my I.T. department in a map service/web map.

We were surprised to see that deleting the field broke the layer in the map service. The layer no longer displays in the Portal web map.

ArcGIS Server Logs: 

There is an error during the draw ACTIVE TRANSPORTATION NETWORK (1.) Underlying DBMS error [ORA-00904: "B",(2.) "COMPLIANCE_STANDARD": invalid identifier] [ACTIVE_TRANSPORTATION].

We're not sure why the removal of that field would cause an error if it’s not used in symbology/labelling/definition queries in the map service/web map. The service had to be restarted to get the layer working again.

If the field isn't explicitly used anywhere in the service, then I don't think deleting it should break the map service layer. Could that behavior be changed?

This isn't my area of expertise. But it caused some grief for our users, so I wanted to suggest it as an improvement.

[NinaRihn]

If the service is published with the field in it, then it is technically being "used", because the service definition itself contains all layers that are turned on in the source document when it is published.  So when the service is used, it is looking for all of the fields in its definition.   So that is why it will throw an error if you remove that field in the underlying dataset.

[A_Wyn_Jones]

This is already implemented through schema locking (disabling it) on the map service:

https://enterprise.arcgis.com/en/server/latest/administer/windows/disabling-schema-locking-on-a-map-...

It must be done while the map service isn't under load to prevent unexpected behaviour for users as the field disappears from existence mid-session 🙂 i.e. when looking at the attribute table

All fields are picked up when initialising the map service in your map viewer session.

[Bud]

@A_Wyn_Jones Thanks. We'd already disabled schema locking. That doesn't seem to have helped.

We don’t really understand how disabling schema locking fixes this problem. 

If anything, if schema locking was turned on, we would have had to stop the service > remove the fields > restart the service. And I think the service wouldn’t have “broken” that way.

[RandyCasey]

@Bud I feel this, but having managed our Enterprise Geodatabase for over a decade, and having made every mistake in the book, and then some; on this exact subject, what I tell my staff is, "if you make ANY changes to the table schema, no matter how minor, you must republish the service or it WILL cause errors". And yes, the system will sometimes allow you to make schema changes to tables, even when schema locking is on. I don't pretend to know how or why, all I know is that I have done it, and then had to fix my mistake afterwards. 

[Bud]

@RandyCasey Good advice.

Side note, regarding, "if you make ANY changes to the table schema...":
I like your term "table schema." In the Esri world, schema changes mean table design changes. But in the Oracle DBA world, schema is another word for the user/data owner. So, it can cause a lot of confusion when you tell your DBA that you've made schema changes, even though you wouldn't/couldn't change the Oracle schema/user/data owner.
So if we say table schema, then that would hopefully mean the same thing to GIS people and Oracle DBA people -- we're making a change to the table design.