这篇文章,在于提醒不要用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.5This 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/