Select to view content in your preferred language

How to repair an SDE Append and Delete versioning table

2991
5
08-19-2015 12:37 PM
JoseSanchez
Frequent Contributor

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;

0 Kudos
5 Replies
AsrujitSengupta
Deactivated User

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.

0 Kudos
JoseSanchez
Frequent Contributor

Yes I am referring to the Add and Delete tables.

Thank you!!

0 Kudos
VinceAngelo
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

0 Kudos
JoseSanchez
Frequent Contributor

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

0 Kudos
VinceAngelo
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

0 Kudos