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.