topic Re: Ora-29876 while trying to compress geodatabase in Oracle in Data Management Questions

Ora-29876 while trying to compress geodatabase in Oracle

VedranDevcic — Wed, 10 May 2017 07:22:50 GMT

Hi,

We are unable to compress geodatabase with ArcCatalog 9.2. Our infrastructure is:

Geodatabase stored with ArcSDE 9.3.1 in Oracle 11g
ArcCatalog 9.2 client

We get the following error (screenshot attached):

ORA-29876: failed in the execution of the ODCINDEXDELETE routine

ORA-00933: SQL command not properly ended

ORA-06512: at SYS.DBMS_SQL line 1053

...

at SDE.ST_DOMAIN_METHODS, line 1969

Any help appreciated.

Re: Ora-29876 while trying to compress geodatabase in Oracle

KevinDunlop — Fri, 12 May 2017 13:24:48 GMT

In current release of ArcGIS desktop, connecting and performing actions in a lower client to a higher GDB isn't normally a problem. However, in earlier release it was. So I would first try upgrading your client to at least 9.3.1 to match your database, or even to version 10.X. Also remember that 9.3.1 does not support the most current version Oracle 11g and requires special patches. So you will need to make sure that your SDE is compatible with the database version you are using.

If you can't upgrade, then the question is, has the compress worked in the past? If so, what has changed since the worked last? This would be the mostly problem area.

Finally, read this post. It might be a bad index. Underlying DBMS error [ORA-29875: failed in the execution of the ODCIINDEXINSERT rout

Re: Ora-29876 while trying to compress geodatabase in Oracle

VedranDevcic — Fri, 12 May 2017 13:43:23 GMT

Thank you for trying to help.

Unfortunately, we've tried with ArcGIS Desktop catalog version 10.4.1 now and the error is the same.

Executing: Compress "Database Connections\Connection to gzora.sde"

Start Time: Fri May 12 15:38:32 2017

ERROR 999999: Error executing function.

Underlying DBMS error [ORA-29876: failed in the execution of the ODCIINDEXDELETE routine

ORA-00933: SQL command not properly ended

ORA-06512: at "SYS.DBMS_SQL", line 1053

ORA-06512: at "SDE.SPX_UTIL", line 1844

ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1969] [SDE.DEFAULT]

Failed to execute (Compress).

Failed at Fri May 12 15:39:35 2017 (Elapsed Time: 1 minutes 2 seconds)

Re: Ora-29876 while trying to compress geodatabase in Oracle

George_Thompson — Fri, 12 May 2017 14:12:32 GMT

I am impressed that it connected to your 9.3.1 GDB. I would not expect that tool to work with a 10.4.1 client. Client and geodatabase compatibility—ArcGIS Help | ArcGIS Desktop

Can you try with a 10.2.1 with the UTUP 7 patch? Esri Support 10.2 (10.2.1, 10.2.2)

Also are you making a direct connection to the Oracle GDB?

https://community.esri.com/groups/geodatabase?sr=search&searchId=51fa0be8-bdca-47d7-95ff-0597c38ea254&searchIndex=0 https://community.esri.com/community/gis/enterprise-gis?sr=search&searchId=498d85ab-64ce-4231-b0f1-19a47af85e81&searchIndex=1‌

Re: Ora-29876 while trying to compress geodatabase in Oracle

VedranDevcic — Fri, 26 May 2017 11:07:52 GMT

So there were 2 problematic domain indexes of type ST_GEOMETRY which needed to be recreated. Unfortunately they couldn't be caught by simple issuing the query

SELECT owner,index_name,table_name from all_indexes where domidx_status !='VALID' or domidx_opstatus!='VALID';

to database views, I had to use my Oracle hacking skills to catch the perpetrators.