How to repair an SDE Append and Delete versioning table

2545
5
08-19-2015 12:37 PM
Highlighted
Occasional Contributor III

Hello everyone,

We are using Oracle 11 G.

The Append and Delete versioned tables form one feature class have a text field with a length smaller than the same field in the feature class. the reason isthat I run an Oracle script to alter this field size on the feature class.

Is there a way to resize this same field in the table Annnn  and Dnnnn with an SDE command?

Can I run in Oracle:

ALTER TABLE

    Annnn

    MODIFY

    (

     [myfield] nvarchar2(80)

    );

 

commit;

Reply
0 Kudos
5 Replies
Highlighted
Regular Contributor III

I believe that you are referring to the Add and Delete tables.

Yes, just increasing the length should not be a problem, I think. However, keep a backup of the data as a precaution.

Reply
0 Kudos
Highlighted
Occasional Contributor III

Yes I am referring to the Add and Delete tables.

Thank you!!

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

The Dn table does not contain business table attributes, only the An does.

The root issue here is using database tools on tables registered with the geodatabase.  You need to be very careful to avoid corrupting geodatabase metadata (field sizes are maintained in multiple locations within SDE and GDB tables).

Best practice is to only use geodatabase-aware tools for these sorts of changes (or unregister versioning, clone the table, drop the table with ArcGIS, rename the clone, alter the clone, then re-register it and re-version it).

- V

Reply
0 Kudos
Highlighted
Occasional Contributor III

Hi Vince,

I was thinking about this solution:

  • wait until all versions are posted,
  • compress database
  • unversion feature dataset where the feature class is located with the discrepancy
  • version feature dataset where the feature class is located with the discrepancy
  • the new Add and Delete tables should have the field with the new length nvarchar2(80)

I do not know the commands that create, rename or alter  clones.  Could you please explain how they work

Thanks

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

There are a zillion ways to make a table clone, but the easiest is to just

CREATE TABLE bar AS SELECT * FROM foo

(This of course assumes you have native geometries [MDSYS.SDO_GEOMETRY or SDE.ST_GEOMETRY], otherwise you need to use an export/import mechanism that is geometry-aware.)

The RENAME syntax is in the Oracle documentation (I didn't memorize that). (And RENAME use is also predicated on native geometry)

- V

Reply
0 Kudos