topic Re: Error Using the 'Analyze' tool with the 'sde' account in Data Management Questions

Error Using the 'Analyze' tool with the 'sde' account

PF1 — Tue, 05 Feb 2013 17:03:02 GMT

We have a few Oracle 11g databases deployed to a SLES 11 environment that are running ArcSDE 10.0 SP4

Here is the message I get when I try to run the 'Analyze' tool using the 'sde' account (using ArcGIS Desktop 10.0 SP4):

Messages Executing: Analyze "Database Connections\SDE_ACCOUNT.sde\DATA_OWNER_ACCOUNT.TABLE_NAME" BUSINESS Start Time: Tue Feb 05 07:20:10 2013 ERROR 999999: Error executing function. Underlying DBMS error [ORA-20000: Unable to analyze TABLE "DATA_OWNER_ACCOUNT"."TABLE_NAME", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 23818 ORA-06512: at "SYS.DBMS_STATS", line 23880 ORA-06512: at line 1 ] Failed to execute (Analyze). Failed at Tue Feb 05 07:20:11 2013 (Elapsed Time: 1.00 seconds)

I can successfully run the 'Analyze' tool using the data owner account:

Messages Executing: Analyze "Database Connections\DATA_OWNER_ACCOUNT.sde\DATA_OWNER_ACCOUNT.TABLE_NAME" BUSINESS;ADDS;DELETES Start Time: Tue Feb 05 07:19:31 2013 Succeeded at Tue Feb 05 07:19:33 2013 (Elapsed Time: 2.00 seconds)

I had my oracle DBA grant the 'ANALYZE ANY' priv to the 'sde' account and now get the following error when trying to Analyze with the 'sde' account:

Messages Executing: Analyze "Database Connections\SDE_ACCOUNT.sde\DATA_OWNER_ACCOUNT.TABLE_NAME" BUSINESS;FEATURE;ADDS;DELETES Start Time: Tue Feb 05 08:48:20 2013 ERROR 999999: Error executing function. Table is not multiversion, but must be for this operation Failed to execute (Analyze). Failed at Tue Feb 05 08:48:22 2013 (Elapsed Time: 2.00 seconds)

I can still successfully Analyze with the data owner account, but it seems that I should be able to do this with the sde admin account as well. Documentation on the Analyze tool: http://help.arcgis.com/en/arcgisdesktop/10.0/help/#/Analyze/0017000000n2000000/

Any help/advise? I've worked the data owner account into our Analyze->Compress->Analyze workflows, but we will run into some of these issues when we have more than 1 data owner and the tables that are registered as versioned and are being edited.

It almost seems that the 'sde' account does not quite see the 'a' and/or 'd' tables (delta tables). Maybe?

Thanks in advance!

Re: Error Using the 'Analyze' tool with the 'sde' account

VinceAngelo — Tue, 05 Feb 2013 17:20:22 GMT

The SDE account should be very limited, and should NOT be able to update
statistics on spatial tables. The table owner should be the only account
used for statistics management.

- V

Re: Error Using the 'Analyze' tool with the 'sde' account

PF1 — Tue, 05 Feb 2013 20:45:35 GMT

The SDE account should be very limited, and should NOT be able to update
statistics on spatial tables. The table owner should be the only account
used for statistics management.

- V

Hi Vince - thank you for the input. We use the SDE account for many database related administration tasks such as the compress and version management (the default version is 'owned' by sde and protected by our workflows so the rec&post needs to be executed with the sde account). ESRI recommends running an Analyze before and after each compress: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002n00000059000000

When you update the statistics for a feature dataset, statistics for all the feature classes in that feature dataset are updated. If the feature dataset contains a geometric network, the network tables are also updated.

You should update statistics before and after a compress operation, after you add or remove topology rules, and after you have finished importing, loading, or copying data into an ArcSDE geodatabase.

So it seems that if the SDE/DB admin wants to automate the Analyze->Compress->Analyze (we are scheduling weekly) that they should use the 'sde' account instead of the data owner account, since there can be many different owners to the data, and since the compress is handled by the same SDE account. SDE/DB admins do not always have the connection parameters (passwords) to user accounts who may own the data.

I was looking to use the 'Analyze Datasets' tool as documented here: http://resources.arcgis.com/en/help/main/10.1/#/Analyze_Datasets/00170000014z000000/ but we are still on ESRI version 10.0 for some of our solutions/applications.

For future reference: It looks like the 'Analyze Datasets' tool documents that account must be the data owner as well:

Note that the connected user must be the data owner for the datasets provided.

Thanks for the input. We will continue to use the data owner account and keep this in mind for future implementations where we may have multiple accounts which 'own' the data.