topic Re: Potential Problems Truncating/Deleting Record in an SDE layer from oracle creating Orphans in Geodatabase Questions

Potential Problems Truncating/Deleting Record in an SDE layer from oracle creating Orphans

keithbickers1 — Fri, 21 Aug 2015 20:34:39 GMT

For various reasons we have the need to delete and insert
records directly through Oracle SQL for a feature class with spatial data. While exploring this option I have been
warned against it because there may be orphan records that are created through
ESRI that will not be created/deleted as needed when these actions are not
completed through the GIS tool. Is this
the case? If so, are there additional updates that could also be done directly
in Oracle to account for this?

We have performed a test on a small dataset to ensure that
we can complete the insert/delete/truncate commands in Oracle and still have
the dataset be readable through ArcGIS, no issues there. Just want to make sure we are not setting
ourselves up for issues in the future by doing this.

Thank you for your input in this matter.

Re: Potential Problems Truncating/Deleting Record in an SDE layer from oracle creating Orphans

VinceAngelo — Sat, 22 Aug 2015 15:24:16 GMT

You are setting yourself up for issues in the future, but it is possible to maintain a simple feature class exclusively from SQL. "Simple feature class" means:

Table is registered with ArcSDE with a USER-set registered rowid (sdelayer -o register -C objid,USER...)
Feature class is NOT versioned
Feature class is NOT archived
Feature class does NOT participate in geodatabase behaviors (relationship classes, feature datasets,...)

In essense, just a stripped down, native table with geometry (SDE.ST_GEOMETRY or MMDSYS.SDO_GEOMETRY).

It's theoretically possible to not trash tables with SDE-set rowids, if you honor all the restrictions in ID allocation, but that would likely tempt you into something which would regularly corrupt your geodatabase.

- V