How do you manage columns in an ArcSDE/Oracle feature class?

Question

Our enterprise has a long history with Oracle and ArcSDE. And lots of tension between the Oracle people and GIS people. Oracle folk often don't like the requirement of using expensive 'nonstandard' tools (e.g. anything other than TOAD or SQLPLUS) to manipulate business tables. But I digress already!

So for example today I wanted to do something simple - a column in a 15-year old non-versioned low-precision sdebinary feature class was marked as non-nullable. Recent editing clients don't use that column any more, but for various reasons we don't want to drop it. So I wanted to change the column to nullable.

1) do it in TOAD easy peasy. Even if the feature class is locked by ArcMap sessions or ArcGIS Server map services. That worked. BUT - this does not update the geodatabase system tables. SDETABLE -o describe shows consistent with with what TOAD shows, but ArcGIS still sees the column as non-nullable. The column_registry.object_flags column (see Appendix E, Administering ArcSDE for Oracle) shows '4' for this column. Whatever that means.

2) do it in the SDE commandline tools - SDETABLE -o alter_column. That worked too, but first required me to identify locks on the feature class, track down which ArcGIS Server services include layers connecting as those users, stop them, then run the command. Lots of literal running around. AND - this also does not update the geodatabase system tables, so ArcGIS still sees the column as non-nullable.

3) use ArcCatalog 10.1 feature class properties dialog? In my experience everything it tells you about an ArcSDE column is read-only. So I guess not.

4) a Toolbox tool? I didn't find a handy 'change column metadata' tool in 10.1.

4) anything else I missed? What will you do when the commandline tools go away? Are things any better in 10.2?

Answer 1

There is some guidance within these related questions:

• Changing field type with ArcPy?
• How to update the length property of a feature class field?

To summarize, your options are modifying columns in the DBMS (not recommended), dropping and recreating just the column, or dropping and recreating the feature class/table.

The last option is what my organization follows within our standard operating procedures. Typically we make schema changes with ArcGIS Diagrammer using the workflow described in this answer. I didn't include it, but we also have to revoke and re-grant privileges to the table. This workflow may be more work, but it is easier for non-DBAs to perform and less likely to cause problems.