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

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

Today I have experienced a new error in my database. We have oracle jobs scheduled to analyze the tables in a schema(PNYDV) , runs in frequent intervals. The job is created in the system schema. The code is as follows:

SQL>conn system/******

SQL> sho user

USER is "SYSTEM"

SQL>   DECLARE

  2      X NUMBER;

  3    BEGIN

  4      SYS.DBMS_JOB.SUBMIT

  5        ( job       => X

  6         ,what      => 'GATHER_ PNYDV_STATS;'

  7         ,next_date => to_date('24/11/2011 07:22:18','dd/mm/yyyy hh24:mi:ss')

  8         ,interval  => 'TRUNC(SYSDATE+7)+8/24'

  9         ,no_parse  => TRUE

 10        );

 11      SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

 12    END;

 13    / 

  commit;

We created a procedure (GATHER_ PNYDV _STATS) to analyze the schema PNYDV in the system schema. It is as follows

SQL> CREATE OR REPLACE PROCEDURE "GATHER_PNYDV_STATS" AS

  2  BEGIN

  3    EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=2147483647';

  4    EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=2147483647';

  5    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PNYDV',method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', CASCADE => TRUE);

  6  END;

  7  /

Procedure created.

Error:

The job ran as per the scheduled time. We got an alert in the logfile when the job ran as per the scheduled time, an error occurred in the alert log file as in the below format.

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

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

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

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

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

ORA-06512: at "SYSTEM.GATHER_ PNYDV_STATS", line 5

ORA-06512: at line 1

I researched on the error but I did not get the result. Then I googled for the solution and got to know that the system user should have the privilege ANALYZE ANY to analyze the non system tables(other schema tables). I granted the ANALYZE ANY  privilege to system user and rescheduled the job. It ran successfully.

SQL> conn / as sysdba

Connected.

SQL> grant ANALYZE ANY to system;

Grant succeeded.

SQL> conn system/*****

Connected.

SQL> exec GATHER_PNYDV_STATS;

PL/SQL procedure successfully completed.

Cause:

                SYSTEM user doesn’t have the privilege to analyze any non system table(Other schema’s table).

Solution:

·         Grant the ANALYZE ANY privilege to the SYSTEM user.

·         Create the procedure and the job under the particular schema which has to be analyzed (under PNYDV schema in my case)