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);

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;

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;

Then we gather schema statistics again.

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

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

SQL> analyze table sh.customers validate structure;

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.

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>
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)

Recently I have faced an issue when application team is executing gather stats in their job.

An application job is loading the data into tables and after that it is performing gather stats. They received below error during gather stats

ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 34634
ORA-06512: at line 1

There are two steps to resolve this issue.

Step 1: Disabling Global preferences

check for the stats global preference value using below query

SELECT DBMS_STATS.get_prefs(‘CONCURRENT’) FROM dual;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)
—————————————————————————————————-
OFF

By output, we can understand that it is OFF and we can turn it on

BEGIN
DBMS_STATS.set_global_prefs (
pname => ‘CONCURRENT’,
pvalue => ‘ALL’);
END;
/

Instead of ALL (which works for both manual and automatic stats collection), we can also set below values
MANUAL – only for manual stats collection
AUTOMATIC – only for automatic stats collection

Step 2: Granting roles to the user

If you see that Global preferences value is already set to ALL, you need to grant below mentioned roles to the user which is performing gather stats.
These grants are not default, so users will face issues if they use concurrent statistics.

SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO testuser;

Once both of above steps are done, issue is resolved.

1. Раскройте ошибку.
Следующая ошибка была обнаружена в файле alert.log Oracle10.2.0.1.

ed Jul  8 22:00:08 2009
Errors in file /export/home/oracle/admin/ora10g/bdump/ora10g_j000_1472.trc:
ORA-12012: error on auto execute of job 8888
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554

*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2009-07-07 22:00:08.968
*** MODULE NAME:(DBMS_SCHEDULER) 2009-07-07 22:00:08.968
*** SERVICE NAME:(SYS$USERS) 2009-07-07 22:00:08.968
*** SESSION ID:(1005.30306) 2009-07-07 22:00:08.968

SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;

*
ERROR at line 1:
ORA-20000: Content of the tablespace specified is not permanent or tablespace
name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
ORA-06512: at line 1

SQL> select distinct tablespace_name from DBA_AUTO_SEGADV_CTL;

TABLESPACE_NAME
------------------------------
TBS_DNINMSV30
...


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
...
TBS_DNINMSV3

SQL> select count(*) from DBA_AUTO_SEGADV_CTL where tablespace_name = 'TBS_DNINMSV3';
COUNT(*)
1

SQL> select segment_owner, segment_name, status from DBA_AUTO_SEGADV_CTL where tablespace_name='TBS_DNINMSV3';
SEGMENT_OWNER SEGMENT_NAME STATUS
BEING_PROCESSED

5. Решить проблему в три этапа (Справочный документ）
Создайте табличное пространство, которое существует в DBA_AUTO_SEGADV_CTL, но на самом деле не существует (достаточно 100 КБ, цель заимствовать его «имя»):

    create tablespace TBS_DNINMSV30 datafile '/export/home/oracle/tmp/dninsmv30temp.dbf' size 100k;

пробег

    exec dbms_space.auto_space_advisor_job_proc;

—- Конечно, об ошибке не сообщается
Удалить табличное пространство

    drop tablespace TBS_DNINMSV30

 
6. Проверьте еще раз
пробег

    exec dbms_space.auto_space_advisor_job_proc; 

delete DBA_AUTO_SEGADV_CTL where tablespace_name='TBS_DNINMSV30';

После запуска exec dbms_space.auto_space_advisor_job_proc; TBS_DNINMSV30 появится снова