For an Oracle APM Database, why does the Stats Aggregation call to dbms_stats.gather_table_stats fail with error "java.sql.SQLException: ORA-20000: Unable to analyze TABLE "USERNAME"."TS_ST_TU_US_INT"..., insufficient privileges or does not exist."

Document ID : KB000056923
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

For an Oracle APM Database, why does the Stats Aggregation call to dbms_stats.gather_table_stats fail with error "java.sql.SQLException: ORA-20000: Unable to analyze TABLE "USERNAME"."TS_ST_TU_US_INT"..., insufficient privileges or does not exist."

 

Issue:

For an Oracle APM Database, the Stats Aggregation call to Oracle procedure dbms_stats.gather_table_stats fail with this error:

[Manager.com.timestock.tess.services.processors.StatsAggregator] [main] [com.timestock.tess.util.TessLogger] Statement failed '{ call dbms_stats.gather_table_stats(?,?,?,?) }'

java.sql.SQLException: ORA-20000: Unable to analyze TABLE "USERNAME"."TS_ST_TU_US_INT" TS_ST_TU_US_INT_"date", insufficient privileges or does not exist

ORA-06512: at "SYS.DBMS_STATS", line 24270

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

 

Solution:

The error message is caused by the Oracle user executing the procedure dbms_stats.gather_table_stats  while not being the owner of the Oracle APM schema.

The Stats Aggregation process will run as the Oracle user specified at EM install time and subsequently set in the EM_HOME/config/tess-db-cfg.xml file. If that user is setup to have access to the APM schema but is not the actual APM schema owner, then the ANALYZE privilege described in the CA APM Installation and Upgrade (How to Install an Oracle APM Database) will not be adequate and instead that Oracle user requires "ANALYZE ANY" privilege.