ORA-20000 While Executing dbms_stats.gather_schema_stats

这篇文章，在于提醒不要用system,sys用户去创建其他schema的索引。

[@more@]

ORA-20000 While Executing dbms_stats.gather_schema_stats [ID 309834.1]
		修改时间 08-NOV-2007 类型 PROBLEM 状态 MODERATED

In this Document
Symptoms
Cause
Solution

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on any platform.

Symptoms

ORA-20000 while executing "dbms_stats.gather_schema_stats"
Connect as user ABC

User ABC has DBA role granted

DBA role includes "analyze any" system privilege

Also granted "analyze any" privilege explicitly to user ABC

SQL> exec dbms_stats.gather_schema_stats('SCOTT',degree=>4,cascade=>TRUE,estimate_percent=>35);
BEGIN dbms_stats.gather_schema_stats('SCOTT',degree=>4,cascade=>TRUE,estimate_pe
rcent=>35); END;
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

Cause

Even if user ABC (or any other user) has DBA Role granted to it(which includes "analyze any" system
privilege),the user cannot analyze any objects that belong to SYS schema

If the schema that is being analyzed(in this example,SCOTT) has any dependent objects belonging to SYS Schema,we will get this error
The small test case below will justify it

SQL> select index_name,owner from all_indexes where table_name='TOM' and table_owner='SCOTT';

INDEX_NAME OWNER
----------------- --------------------------
IDX1 SYS

Note here that an index exists (owner is SYS) on a table owned by SCOTT
SQL> conn
Enter user-name: ABC/ABC

Connected.

Note:The user ABC has DBA granted to it...select count(*) from session_privs returns 139 rows

SQL> exec dbms_stats.gather_schema_stats('SCOTT',degree=>4,cascade=>TRUE,estimate_percent=>35);
BEGIN dbms_stats.gather_schema_stats('SCOTT',degree=>4,cascade=>TRUE,estimate_pe
rcent=>35); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 10583
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

Solution

To implement the solution, execute the following steps:
1. Drop the index that belongs to SYS schema
SQL>drop index

To identify the table that has problem,execute the following plsql

SQL>set serveroutput on

SQL>
begin
for x in (select table_name from dba_tables where owner='SCOTT')
loop
dbms_output.put_line('TABLE : ' || x.table_name);
begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
x.table_name, cascade => TRUE, estimate_percent => 35);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;
For the above case,we will see :

TABLE : TOM
### ERROR: ORA-20000: Object IDX1 does not exist or insufficient privileges to analyze
.
Now,get the index using all_indexes

SQL>select index_name,owner from all_indexes where table_name='TOM' and table_owner='SCOTT';

It will return IDX1 as index_name and SYS as owner

2.Execute dbms_stats now.
SQL> exec dbms_stats.gather_schema_stats('SCOTT',degree=>4,cascade=>TRUE,estimate_percent=>35);

3.Recreate the index in SCOTT schema

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3345/viewspace-1029368/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3345/viewspace-1029368/