nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Feature class stuck in LOAD-ONLY mode due to duplicate keys being found [ORA-01452]

TimLangner

Hi there

Some months ago I created a feature dataset and within this a feature class. This was created through ArcSDE 9.3.1, via ArcCatalog 10. The back end being Oracle 10g. With this feature class I enabled archiving and topology. It is of course versioned.

The other week I upgraded my computer to ArcGIS 10.1 and carried on working with the feature class. The following morning I suddenly found I could not edit the data. The data was now stuck in load only mode. I rang the UK technical support and myself and another colleague followed the steps to take it out of load only mode.

The commands being:
sdelayer -o describe -l LAYER_NAME, shape -u sde -p sde

sdelayer -o normal_io -l LAYER_NAME,shape -u sde -p sde

However the second command then generated the error:
Error: Underlying DBMS error (-51).
Error: Setting Layer to NORMAL I/O mode.
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
(A2868_IX1)
Layer is still in LOAD-ONLY mode!

Since then myself and one of the UK technical support staff have been unable to find out what is going on. Using SQL Developer I have gone through various tables stored in Oracle and can find no duplicate keys on the columns I've been checking, although I may be checking the wrong columns of course. There is no A2868 table in existence but there is an F2868 and S2868, these referencing the archiving table
Does anyone know which column or table might have the offending duplicate? Would it be an A or D table or simply the LAYER_NAME or LAYER_NAME_H tables?

If I check the shape column in the archiving table then I found 541 duplicates.
select count(*), shape from LAYER_NAME_H
GROUP BY shape
HAVING COUNT(*) >1;

However I am not even sure if that is the correct column and being the archive table, I might expect duplicates.

Whilst it would be possible to recreate the feature class, archives cannot be recreated; copied or anything much done to them apart from making them no longer an archive. So removing the archive isn't an acceptable solution in my opinion but it may be what I have to put up with if an answer can't be found. However I don't want to start again, only to find the problem reoccurs.

Kind regards

Tim

Vind meer berichten getagd met

Accepted answers

All comments

TimLangner

Hi there

I think I have found a possible cause of this problem. When creating the feature class I created three text columns with the figure 1073741822. From memory that was the largest figure I could enter into ArcCatalog. I was never going to reach that figure but I just wanted the largest number so that I wouldn't run out of space when typing.

Even now when I go into ArcCatalog it tells me the column is a text column. Actually it is an NCLOB column in Oracle and I believe this is what is causing my problems below, even though my text it no where near that limit.

What happens is that when I try to sort one of the columns in ArcMap, which has this high value, I get the following error message:
Underlying DBMS error [ORA-00932: inconsistent datatypes: expected - got NCLOB]

Unfortunately it doesn't tell me what it expected.

There is another thread I put up about this, when I didn't know these two problems were linked:
http://forums.arcgis.com/threads/69452-What-does-following-mean-ORA-00932-inconsistent-datatypes-expected-got-NCLOB?p=252904#post252904

When I went to save the data today I then got the above error and below it:
Underlying DBMS error [ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found (A2868_IX1)].

I have an on going support call with ESRI UK technical support and they are now going to try and recreate the problem at their end, now that I think I have found a way of replicating the issue.

If I find out more I will update this in case it is of help to other users experiencing the same problem.

Kind regards

Tim

VinceAngelo

The documentation covers naming rules. The An_IX1 index is on the geometry column of the
business table of a layer, meaning that you have the same SDEBINARY/SDELOB external key
(SHAPE value) in multiple rows. This is very bad, and should only occur if some external
mechanism was used to populate rows in the table (or worse, if the tablespace datafile has
been corrupted).

It is not possible that using a LOB column would corrupt the business table geometry column.
It *is* possible that whatever corrupted your instance mangled both the business table and the
SDE.COLUMN_REGISTRY table (this is especially true if they are both in the same tablespace).

If your table *is* damaged beyond recovery, when you start over, be sure to avoid use of the SDE
user for ownership of any geometry tables. The SDE user should be exclusively reserved for
ArcSDE administration (for much the same reason as not using the SYSTEM user for general use).
Instead, create one or more tablespaces for geometry data and user to own spatial data (with
a default tablespace other than SDE).

- V

TimLangner

Thanks for your reply.

No external mechanism was used to populate the table with data, other than ArcMap itself. I did enable topology and archiving in ArcCatalog but that was it. The problem occurred on the day I upgraded to ArcMap 10.1.

The latest copy of data itself exists in Oracle okay because I was able to copy it to a new feature class.

At the time I could not edit the data. Just now however I found I could. I am not sure why I can edit it now but I couldn't then. However when doing the below command it is still saying that the LAYER_NAME in question is in load_only mode, despite the fact I have just created a feature in it and then saved it.

sdelayer -o describe -l LAYER_NAME, shape -u sde -p sde

It would be interesting to know what caused the problem. Was it an attempted reordering of the NCLOB formatted column? Someone from UK technical support has been helping with this. It's taken a while because I was waiting at my end for backups from before the problem occurred, which I may not now require, if the problem is resolved. The support person is now seeing if they can produce the same problem as me with another feature class.

I did think that all that might need to happen is the removal of the duplicate keys. The problem was I couldn't find the duplicate keys. I did not enable topology or archiving on the new feature class. in case it ended up with the same problems as the last one.

Out of interest, what is wrong with using the user SDE to store feature classes? I am not the administrator here but would pass on any information to them.

VinceAngelo

It's far easier to write about what's *right* about using the SDE user for spatial data:
1) Nothing.

But let me give some highlights:
1) Anyone with SDE user password can destroy the integrity of the instance (which is
also why it's unwise to make the 'SDE' user password 'sde')
2) The SDE tablespace should not be fragemented with spatial data (and vice versa)
3) Best practice involves using multiple users, with data tables owned by logical data
sources (e.g., PLANNING, WATER, TAXATION, BASEMAP), with login accounts for
individual users (no shared passwords), and roles to grant access by logical duties.
4) Only senior staff, trained in geodatabase design and organizational DBTUNE
implementation, should be permitted to create new tables with ownership accounts
(they'd also be required to perform necessary GRANTs to access roles). [This also
provides a way to prevent unintended datatypes from being used -- tables can be
developed in user accounts and standardized before being shared.]

Of course, implementing best practice also involves more active participation of "DBAs"
in the workings of the "GIS stuff", and requires that GIS staff have more access to
critical tools, like the command-line ArcSDE utilities.

- V

AndreaB__1-1729094214984.png

JonathanDandois_0-1729094261438.png

TimLangner

Hi vangelo

Thank you for your reply. I believe the use of the SDE user goes back some years. Other Oracle users are in use now but there is still some data as the sde user and it is used at times when data is being created. I will pass on the comments.

As I found I could edit the data I then went to another computer running ArcMap 10.0.4. On that Ioaded up a 9.3.1 version of my MXD from late September. Then I went to edit the data but it wouldn't let me.

Interestingly when I try to open this MXD in 10.1.1, I get the error:
"Opening the selected ArcMap document failed:

The data frames of the map document could not be read.

The data frames of the map document could not be read due to the following error:
ox80040111 (ClassFactory cannot supply requested class)

The last successfull loaded component was:
Unknown ProgID ({FFFF0000-0000-0000-79CC-56770840FF04})

The specified file does not contain a valid ArcMap Document."

Yet the very same document opens in version 10.0.4. My ArcMap 10.1 is a fresh install last week with no customisation or add on install expecting for some of the things that come with the downloaded iso image. The computer was reimaged before that with no previous ArcMap installed either. Also of interest but very unlikely to be related to this, is that my current MXD becomes unstable in ArcMap after zooming and panning for 2-4 hours. The more panning and zooming I do per minute, the quicker it becomes unstable. However this is only for versions of ArcMap from 10.0.5 onwards. It works fine for all versions before 10.0.4.

What makes that more intriguing is that when I created a new file and just added the layers I was currently displaying and didn't include the previous layout, it appeared that the MXD was stable. However I've not done enough panning and zooming yet to be sure this is really the case.

Following on from above. I took my 10.1 version MXD and saved a copy as a version 10. I opened it in ArcMap 10.0.4. The first message I got was about the columing being NCLOB.

Then I tried to edit the feature class and it would not let me. It came up with a message:
Cannot edit the data in the folder or database selected.

Yet this same feature class can be edited in the 10.1 version MXD, from which the version 10 MXD was just created! I hadn't even closed the 10.1 MXD down before attempting this.

My initial thoughts on this are that perhaps there was a change with SP 1 for ArcMap 10.1, which is allowing me to edit the data whilst it is in load only mode. I couldn't do that when the problem first occurred and I was using no service packs. I will forward the information onto the ESRI UK support who are helping me with this.

Kind regards

Tim

TimLangner

"Opening the selected ArcMap document failed:

The data frames of the map document could not be read.

The data frames of the map document could not be read due to the following error:
ox80040111 (ClassFactory cannot supply requested class)

The last successfull loaded component was:
Unknown ProgID ({FFFF0000-0000-0000-79CC-56770840FF04})

The specified file does not contain a valid ArcMap Document."

Just a quick update. With regard to the above error. I pin pointed that down to two basemap group layers. If either were included in my MXD document and I down saved it to a 9.3.1 version, it would not open in 9.3.1 or 10.1. However am able to down save to 9.2 and have that open in 9.3.1 or 10.1. I don't have 9.2 installed to test that.

Therefore it's not related to my LOAD-ONLY mode problems. The load only mode issue has not been resolved yet as awaiting backups of the data prior to the problem to see if that holds any clue.

Kind regards

Tim

TimLangner

Hi there

It was never possible to locate the cause of this problem. Unfortunately for various reasons it was not possible to get hold of a backup from a point before it happened so with ESRI UK support, it was not possible to compare them and see what had changed underneath. So the call had to be reluctantly closed.

Kind regards

Tim

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.