Shawn_Moore

CA Clarity Tuesday Tip: Insufficient Oracle Privileges During Upgrade

Discussion created by Shawn_Moore Employee on Oct 5, 2011
Latest reply on Oct 11, 2011 by Shawn_Moore
Title: CA Clarity Tuesday Tip: Insufficient Oracle Privileges During Upgrade

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 10/02/2011

Today we will look at an error message that can be generated during some upgrades and a way to diagnose this.

The scenario: An upgrade fails with an ORA-20000 error.

i.e.


10/11/11 12:30 AM (admin) ==========================================
10/11/11 12:30 AM (admin) DBTools Log - Tue Oct 11 00:30:06 PDT 2011
10/11/11 12:30 AM (admin) ==========================================
10/11/11 12:30 AM (admin) Command: analyze
10/11/11 12:30 AM (admin) Analyzing the database....
10/11/11 12:30 AM (admin) Using dbms_stats.gather_schema_stats(ownname => 'NIKU',estimate_percent => 50, cascade => true)....
10/11/11 2:00 AM (admin) Total time: 1H:30M:40S
10/11/11 2:00 AM (admin) Exception in thread "main" java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Insufficient privileges to analyze an object in Schema
10/11/11 2:00 AM (admin) ORA-06512: at "SYS.DBMS_STATS", line 13578
10/11/11 2:00 AM (admin) ORA-06512: at "SYS.DBMS_STATS", line 13937
10/11/11 2:00 AM (admin) ORA-06512: at "SYS.DBMS_STATS", line 14015
10/11/11 2:00 AM (admin) ORA-06512: at "SYS.DBMS_STATS", line 13974
10/11/11 2:00 AM (admin) ORA-06512: at "NIKU.CMN_JOB_ANALYZE_SP", line 12
10/11/11 2:00 AM (admin) ORA-06512: at line 1
10/11/11 2:00 AM (admin)
at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown Source)
10/11/11 2:00 AM (admin)
at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source)
10/11/11 2:00 AM (admin)
at com.ca.clarity.jdbc.oracle.OracleImplStatement.execute(Unknown Source)

To diagnose this look at the Oracle event logs, which will usually point out what schema object (i.e. index) the privileges were insufficient for. Once you isolate the object of failure, you can then easily recreate the problem quickly by running a specific gather_table_stats command, which only gathers stats at the table level. If the Oracle trace logs indicated a problem while generating stats on PRTASK, then you login with the user that is used for Clarity (i.e. niku).

e.g.
exec dbms_stats.gather_table_stats(ownname=>'NIKU', tabname=>'PRTASK', estimate_percent=>50, cascade=>true);

The problem can happen if the schema has custom indexes that were created with the system user as the owner. Such problems can be resolved with additional rights granted to your schema (user).

i.e.

grant analyze any to niku;
grant alter any indextype to niku;


Hope this one is useful (it is a bit obscure and specific)

-shawn

Outcomes