Pages

Wednesday 23 November 2011

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)


1 comment:

  1. Thank you for your input, help a lot.
    For people who want to know which object is concerned by the error message go look under user dump dest folder and find the trace file which contained this type of message : DBMS_STATS:
    ORA-20000: Unable to analyze INDEX "BATCHADM"."LIGNES_CONTRAT_BL", insufficient privileges or does not exist

    Cheers

    ReplyDelete