Oracle: Increase field text length on feature class with A and D Tables.

Question

Hello DM'ers

Geodatabase 11.1
ArcGIS Pro 3.1
Oracle 19.16.0.0
ST_GEOMETRY

I have read the documentation on Alter Field as I wish to increase the length of a text field from 100 to 200 characters. The field contains data already and the feature class is editable and so has associated A and D tables.

* Firstly, is it possible to use the alter field tool on feature classes stored in Oracle to increase field length?
* Secondly, if it is possible, will the associated A-Table be updated as well?

Feedback welcome  - thanks.

JamesMorrison1 · Answer

Great detailed answer Marcelo. Much appreciated.
I was only going to use the Alter Field GP Tool never directly with SQL.

MMBR · Answer

FAQ: What Schema Changes Can Be Made to Versioned Data? (esri.com)

Schema changes and archiving—ArcGIS Pro | Documentation

Alter Field (Data Management)—ArcGIS Pro | Documentation

Tips:

- do not make any changes to the tables directly in Oracle via SQL statements, the Geodatabase Repository will not like that and you can corrupt the Geodatabase.

- any fields changes shall be done with the ArcGIS tools

- always take a full backup of your Geodatabase before doing schema changes.

- Oracle Geodatabase the Oracle Database Administrator can create a "guarantee restore point", then if something goes wrong use the "restore point" to flashback the database, and if everything goes fine then drop the "restore point" after the changes.

- if the Alter Field GP Tool does not work for you case or if you have unexpected results then the easy way to proceed is to follow these steps.

0. take a full database backup.

1. create the Oracle Database Guaranteed Restore Point

2. ask all users to disconnect and stop all ArcGIS Server Services

3. use ArcGIS Pro to add the new field, if geodatabase traditional versioning is enabled, then the new field will be added to the A tables as well.

4. use ArcGIS Pro to calculate the new field using the old field, do this in the sde.default version, it might take longer if you have a lot of data, after doing this execute a sde compress connected as the sde user (if using traditional versioning).

5. use ArcGIS Pro to drop the old field.

6. use ArcGIS Pro and run another sde compress connected as the sde user ( if using traditional versioning).

7. if the old field has an index then recreate the index in the new field using ArcGIS Pro.

8. gather new statistics - important must do!!!

9. drop the Oracle Database Guaranteed Restore Point

10. take another full database backup.

- if you have a lot of data and using geodatabase traditional versioning, then consider to reconcile and post all versions child of sde.default, run a sde compress, then delete the child versions, then run another sde compress, then unregister the featureclass/table as traditional versioning, then make the changes ( add new filed, calculate new field with old field, drop old field), then register as traditional versioning again.

A good example of using Oracle Guaranteed Restore Point.

Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point | Download PDF Version

AlexanderDanielPratama · Answer

Based on my experiences using the alter fields gptools

You can fully utilize this tool if the table is empty. If not, it only works with field name and alias. It cannot increase the field length if you have a value inside the specific field. Alternatively, you need to create a new backup field first, with similar properties to the original. Delete the original field, then create a new field that is similar to the old one with updated properties i.e length

If the table is related in the db using relationship class or table view, I am afraid it is already locked. You must break that lock by deleting relationship class or delete table view.

Hope it helps you

Cheers