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)


Tuesday 22 November 2011

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes



ORA-39095 error in Datapump export when using PARALLEL option and single dumpfile

Environment:
10g R2 in Linux Box

I tried to export the database using the below par file, It has only single dumpfile.

USERID=username/*****
DUMPFILE=expdp.stg2514.dmp
FULL=y
DIRECTORY=DATA_PUMP_DIR1
JOB_NAME=expfull_stg2514_new
parallel=5

It ended up with the below error:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."EXPFULL_
stg2514_NEW" stopped due to fatal error at 03:46:40

I googled to get a good solution to overcome the above error, many suggested to remove the parallel option. Then I ran the export by removing the parallel option. It completed successfully without errors. But I did not did not understand why the error occurred if the parallel option is used. I searched many sites for the cause and at last I familiarized that it is a bug (Bug:3328558) in 10g R2, mentioned in Metalink.

Cause:

If the dumpfiles are less than the parallel processes, then the dumpfile may be locked by one process and the other process waits for the dumpfile to write. And sometimes the process may not release the lock even if the process finished. So the entire task will not end as the other processes are waiting for the lock.

Solution:

Parallelism should be less than or equal to the number of the dumpfiles. Or avoid the usage of parallel option.