Problem trying to rebuild indexes and analyze tables using arcpy

8972
6
Jump to solution
07-15-2014 10:43 AM
Ulises
by
Occasional Contributor III

ArcGIS 10.2

Oracle 11g

 

I want to use a python script to batch reconcile every day.  I'm following the steps found in the documentation "Using Python scripting to batch reconciles and post versions".  No problems reconciling, but I'm not able to rebuild indexes and analyze tables using the script.  If I set my connection for the data owner I received a privileges error message...

 

Traceback (most recent call last):

  File "E:\DAILY_TASKS\GDBRECONCILE\CDB_test.py", line 9, in <module>

    arcpy.RebuildIndexes_management(workspace, "SYSTEM", "GDB.Servhist", "ALL")

  File "C:\AG_Home\Desktop10.2\arcpy\arcpy\management.py", line 4723, in RebuildIndexes

    raise e

ExecuteError: ERROR 000684: Do not have administrative privileges.

Failed to execute (RebuildIndexes).

 

If I set the connection for the geodatabase owner, the script runs without any error messages, but looking from the database side the indexes are not recreated and the tables are not analyze.  Using the data owner connection in ArcCatalog there is no problem rebuilding the indexes using the Rebuild button in the properties window of the feature class as well as no issues analyzing the layer.  Is there any particular requirements to run this commands in the script???

 

Thanks in advance for any help...

Ulises Feliciano Troche
0 Kudos
1 Solution

Accepted Solutions
AsrujitSengupta
Regular Contributor III

As for the locks related errors, is there anyone else working with the data from that gdb right now? Is it possible to make sure there are no other connections to that gdb and then re-run that script.

You can also check this link for "analyzing the spatial data and rebuilding their indexes" from the database end:

24518 - How can ArcSDE performance be improved?

View solution in original post

0 Kudos
6 Replies
AsrujitSengupta
Regular Contributor III

In ArcCatalog, open up the Python shell and just run the "Rebuilding indexes and updating statistics" part in it. Check if it actually works there or not.

0 Kudos
Ulises
by
Occasional Contributor III

I received the same error message as when running the script in python when using the data owner connection...error 000684

using the gdb owner (sde), I received the message "Could not rebuild indexes on dataset (Could not get a lock.)".

running the analyze the message is "WARNING 000087: Could not open table Database Connections\sdecd.sde\GDB.ServHist".

Ulises Feliciano Troche
0 Kudos
AsrujitSengupta
Regular Contributor III

The first error "ERROR 000684: Do not have administrative privileges" is expected.

Most of the geodatabase system tables have indexes, but the tables that tend to have the greatest amount of change in a versioned enterprise geodatabase and, therefore, require the indexes to be rebuilt most often are the states, state_lineages, and mv_tables_modified system tables. As the geodatabase administrator, you can rebuild the indexes on these tables in geodatabases in IBM DB2, Microsoft SQL Server, Oracle, or PostgreSQL databases using the Rebuild Indexes geoprocessing tool.

Your usual data_owner is not the geodatabase administrator, so the tool fails. ArcGIS Help 10.1

The geodatabase administrator in Oracle: ArcGIS Help 10.1

0 Kudos
AsrujitSengupta
Regular Contributor III

As for the locks related errors, is there anyone else working with the data from that gdb right now? Is it possible to make sure there are no other connections to that gdb and then re-run that script.

You can also check this link for "analyzing the spatial data and rebuilding their indexes" from the database end:

24518 - How can ArcSDE performance be improved?

0 Kudos
Ulises
by
Occasional Contributor III

I kind of get the expected behaviour explanation rebuilding the indexes, but still not sure why I can't see the updated information in the database side for the tested index when running the script or the gp tool.  In the gp tool it must be the data owner user connection to be able to select the feature class required, which in this case is not versioned.  Based on the documentation, the option should be NO_SYSTEM for any user other than the GDB admin.  Using ONLY_DELTAS or ALL and SYSTEM or NO_SYSTEM provide the same results when using the gdb admin in the script.  Using NO_SYSTEM and ALL in the script with the data owner connection set updates the value for the LAST_DDL_TIME in the database, which suggests the index rebuilding, but the creation time for the index do not update. Yet using the Rebuild button, available from the data owner connection, actually shows the updated information for the object in the database for the fields LAST_DDL_TIME, CREATED and TIMESTAMP in dba_objects.  Maybe I'm overthinking this, but what's the reason?

The same looks true for the analyze gp tool regarding the connection used.  Using the gdb admin connection in the script with or without SYSTEM option included, I don't see updated date for analyze information in the table.  Using the data owner connection in the script, with NO_SYSTEM option (only available), I can see the updated value in the backend, which I guess is the correct option to execute, even for my most edited versioned features classes.  Is this true for these feature classess?  Shouldn't be able to analyze system tables for them?

I would like to include the batch reconcile, rebuilding and analyze in the same script instead of seggregate them using python for the reconcile and other to update statistics in the database, so I will hold the other suggested method for a little more time until we're sure it is the most convenient/effective way.

Ulises Feliciano Troche
EduardoAbreu-Freire
New Contributor III

Ulises Feliciano Troche‌,

Despite you have discussed this subject a kind of long time ago could you please make an update what was the method - including rebuild indexes / analyze datasets / reconcile - you have found more effective?

Dealing with enterprise geodatabases in sql server and the administrator ( SA)  as the connection user have you found problems (hanged, frozen operation) with the rebuilding indexes?

0 Kudos