GATHER_TABLE_STATS 分析分区表报ORA-20000错误问题

13^#

发表于 2012-6-19 09:35:42

确实是权限丢失了，tchat表重建了之后就没问题了，然后自动收集job又报了另一个同样情况表的ORA-20000错误

12^#

发表于 2012-6-18 11:00:40

试了一下不是建表小写的那个情况。
我问了一下应用这个表的情况，这个表是前几天imp导入创建的，我现在怀疑是imp有什么bug导致权限缺失

11^#

发表于 2012-6-17 22:29:22

查了一下 metalink 发现也可能确实由于SYS 用户权限缺失导致该问题，建议你检查 SYS的权限

ORA-20000: Insufficient Privileges On Dbms_stats

Applies to:

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

Automated Gather Stats Job fails with errors :

ORA-12012: error on auto execute of job 8951
ORA-20000: ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2175

Related queries to role-privileges returned :

SQL> conn / as sysdba

select * from ( select r.privilege
from user_role_privs u,role_sys_privs r
where u.granted_role=r.role
union all
select privilege
from user_sys_privs
)
where privilege = 'ANALYZE ANY'
   and rownum < 2;

PRIVILEGE
----------------------------------------
ANALYZE ANY

1 row selected.

SQL> select r.role, r.privilege
      from user_role_privs u,role_sys_privs r
      where u.granted_role=r.role
      and r.privilege='ANALYZE ANY'
      /

no rows selected

SQL> select privilege
      from user_sys_privs
      where privilege='ANALYZE ANY'
      /

PRIVILEGE
----------------------------------------
ANALYZE ANY

1 row selected.

Cause

Several default roles where missing necessary privileges :

SQL> select * from USER_ROLE_PRIVS
   order by granted_role;

USERNAME GRANTED_ROLE ADM DEF OS_
SYS OEM_ADVISOR YES YES NO
SYS SCHEDULER_ADMIN YES YES NO

2 rows selected.

SQL> select * from ROLE_SYS_PRIVS
   order by role;

ROLE             PRIVILEGE                ADM
----------------  -------------------------  ---
OEM_ADVISOR    ADMINISTER SQL TUNING SET  NO
OEM_ADVISOR    ADVISOR                   NO
OEM_ADVISOR    CREATE JOB                NO
SCHEDULER_ADMIN CREATE ANY JOB          YES
SCHEDULER_ADMIN CREATE EXTERNAL JOB       YES
SCHEDULER_ADMIN CREATE JOB                YES
SCHEDULER_ADMIN EXECUTE ANY CLASS       YES
SCHEDULER_ADMIN EXECUTE ANY PROGRAM       YES
SCHEDULER_ADMIN MANAGE SCHEDULER          YES

9 rows selected.

Roles like DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE are not granted to SYS anymore or are lacking the necessary privileges.

Solution

In a clean created 10G database there should be are 297 privileges granted to the SYS-roles :

SQL> select role, count(*)
from ROLE_SYS_PRIVS
group by role
order by role;

ROLE                         COUNT(*)
------------------------------ ----------
AQ_ADMINISTRATOR_ROLE          6
CONNECT                         1
DBA                            160
EXP_FULL_DATABASE                8
IMP_FULL_DATABASE             68
JAVADEBUGPRIV                   2
MGMT_USER                      2
OEM_ADVISOR                      3
OEM_MONITOR                      7
OLAP_DBA                      10
OLAP_USER                      5
RECOVERY_CATALOG_OWNER          11
RESOURCE                         8
SCHEDULER_ADMIN                6

14 rows selected.

Regranting the SYS-privileges to the SYS-roles resolved the ORA-20000

10^#

发表于 2012-6-17 22:00:01

另一种可能性

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0    Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create user icd identified by icd;

User created.

SQL> grant dba to icd;

Grant succeeded.

SQL> conn icd/icd
Connected.
SQL> create table "tchat" (t1 int);

Table created.

SQL>
SQL> conn / as sysdba
Connected.
SQL> exec dbms_stats.gather_table_stats('ICD','TCHAT');
BEGIN dbms_stats.gather_table_stats('ICD','TCHAT'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT", insufficient privileges or
does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15017
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats('ICD','tchat');
BEGIN dbms_stats.gather_table_stats('ICD','tchat'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT", insufficient privileges or
does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15017
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

以小写形式的 create的表名因为 dbms_stats自动转换为大写形式  会导致 ORA-20000错误
可以通过  双引号解决该问题

SQL>  exec dbms_stats.gather_table_stats('ICD','"tchat"');

PL/SQL procedure successfully completed.

尝试用 dbms_metadata.get_ddl 获取 TCHAT表的实际DDL

9^#

发表于 2012-6-15 13:41:36

看了只找到一个情况相符的bug Bug 6766012可惜是10g的版本不符合，帮看一下吧

原帖由 magic007 于 2012-6-15 11:31 发表打开10046事件跟一下。

kfdb2_ora_13014.zip

38.83 KB, 下载次数: 1240

magic007

8^#

发表于 2012-6-15 11:31:24

打开10046事件跟一下。

7^#

发表于 2012-6-15 11:14:50

回复 6# 的帖子

错误提示是可以自己定义的，你想要怎么样都可以的。 ORA-20000就是自定义的错误，你只要找trigger 就是了

6^#

发表于 2012-6-15 10:53:40

谢谢大神不过不是这个原因，错误提示都不一样

5^#

发表于 2012-6-14 23:29:07

[oracle@nas ~]$ 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.

ORA-19999开始的20000是自定义的 application error  一看到20000就知道是  trigger在搞鬼了

create or replace trigger ddl_deny
before analyze on database
declare
  l_errmsg varchar2(100):= 'insufficient privileges or does not exist';
begin
if ora_sysevent = 'ANALYZE' then
raise_application_error(-20000, 'Unable to analyze '||ora_dict_obj_owner || '.' || ora_dict_obj_name || ',' || l_errmsg);
end if;
exception
  when no_data_found then
null;
end;
/

Trigger created.

SQL> analyze table tv compute statistics;
analyze table tv compute statistics
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Unable to analyze SYS.TV,insufficient privileges or does not exist
ORA-06512: at line 5

SQL> alter trigger ddl_deny disable;

Trigger altered.

SQL> analyze table tv compute statistics;

Table analyzed.