How to Resolve ORA-20000: Insufficient privileges to analyze an object in Schema

ORA-20000

A normal user can analyze its own schema without problem, but it cannot analyze other's schema. If do it, ORA-20000 will be thrown.

Let's see the case.

SQL> show user
USER is "HR"
SQL> exec dbms_stats.gather_schema_stats(ownname => 'HR', degree => 4);

PL/SQL procedure successfully completed.

So far so good. How about analyze other's schema.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SH', degree => 4);
BEGIN dbms_stats.gather_schema_stats(ownname => 'SH', degree => 4); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 42210
ORA-06512: at "SYS.DBMS_STATS", line 42165
ORA-06512: at "SYS.DBMS_STATS", line 41983
ORA-06512: at "SYS.DBMS_STATS", line 41163
ORA-06512: at "SYS.DBMS_STATS", line 41930
ORA-06512: at "SYS.DBMS_STATS", line 42115
ORA-06512: at "SYS.DBMS_STATS", line 42196
ORA-06512: at line 1

Theoretically, ORA error number ranging from ORA-20000 to ORA-20999 are customized errors raised from PL/SQL programming units. Let's see its definition.

[oracle@test ~]$ oerr ora 20000
20000, 00000, "%s"
// *Cause:  The stored procedure 'raise_application_error'
//          was called which causes this error to be generated.
// *Action: Correct the problem as described in the error message or contact
//          the application administrator or DBA for more information.

The error code seems to be used by the developer of this native package.

In this case, ORA-20000 means that the user has not enough privileges to analyze other's schema objects. Basically, you can only analyze your own objects, not other's. To analyze other's schema, you need a special system privilege.

Solution

To solve ORA-20000 thrown by DBMS_STATS, you need a system privilege, ANALYZE ANY at schema-level. So we grant it to the user who wants to analyze other's schema by a privileged user.

SQL> show user
USER is "SYS"
SQL> grant analyze any to hr;

Grant succeeded.

Then we gather schema statistics again.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SH', degree => 4);

PL/SQL procedure successfully completed.

Furthermore, you can start to use ANALYZE TABLE statements for other user's table.

SQL> analyze table sh.customers validate structure;

Table analyzed.

Problem fixed.

In fact, I expect it throws ORA-01031: insufficient privileges which is the official error to warn users who did not use the right privilege to do manipulate the database.