ORA-20000:DBMS

今天查看数据库的信息发现如下报警 Thu Mar 23 22:00:02 2017 Thu Mar 23 22:00:09 2017 GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file /u01/app/oracle/diag/rdbms/orcl_pd/orcl/trace/orcl_j003_3811.trc: ORA-20000: Unable to analyze TABLE “OSSC”.”DBMS_TABCOMP_TEMP_UNCMP”, insufficient privileges or does not exist Thu Mar 23 22:00:40 2017 Fri Mar 24 02:00:00 2017

初步来看是作业收集的时候发现某个用户下没有该表,查看跟踪文件:

* 2017-03-23 22:00:09.281 * SESSION ID:(1753.18133) 2017-03-23 22:00:09.281 * CLIENT ID:() 2017-03-23 22:00:09.281 * SERVICE NAME:(SYS$USERS) 2017-03-23 22:00:09.281 * MODULE NAME:(DBMS_SCHEDULER) 2017-03-23 22:00:09.281 * ACTION NAME:(ORA$AT_OS_OPT_SY_2778) 2017-03-23 22:00:09.281

ORA-20000: Unable to analyze TABLE “shark”.”DBMS_TABCOMP_TEMP_UNCMP”, insufficient privileges or does not exist

数据库是 oracle 11.2.0.1 该用户下没有这个表. 网上一查 ORA-200000的信息,好像没有啥可用的.逐渐把关键词设定 “DBMS_TABCOMP_TEMP_UNCMP” 发现是自动空间诊断

应该是信息收集作业

select to_char(t.job_start_time, 'yyyy-MM-dd hh24:mm:ss'), t.job_duration from dba_autotask_job_history t where client_name = 'auto space advisor' order by to_char(t.job_start_time, 'yyyy-MM-dd') desc;

时间上是对上的

TO_CHAR(T.JOB_START_TIME,'YYYY JOB_DURATION 2017-03-23 22:03:02 +000 00:00:09 2017-03-22 22:03:02 +000 00:08:20 2017-03-21 22:03:01 +000 00:00:12 2017-03-20 22:03:02 +000 00:08:46 2017-03-19 06:03:02 +000 00:00:04 2017-03-19 22:03:34 +000 00:00:04 2017-03-19 18:03:21 +000 00:00:03 2017-03-19 14:03:07 +000 00:00:04 2017-03-19 10:03:53 +000 00:00:06 2017-03-18 06:03:02 +000 00:00:04

1.查看自动收集统计信息的任务及状态

sys@ora11g> select client_name,status from dba_autotask_client; CLIENT_NAME STATUS ------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED

2.禁止自动收集统计信息的任务 我们可以使用DBMS_AUTO_TASK_ADMIN包完成这个任务。 sys@ora11g> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’,operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

sys@ora11g> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS ------------------------------------- -------- auto optimizer stats collection DISABLED auto space advisor ENABLED sql tuning advisor ENABLED

此时“auto optimizer stats collection”任务已经被禁用，目的达到。

再一次印证：ORACLE新特性总是BUG多多的！我们应该了解每一个新特性带给我们的便捷和优势，更重要的是我们要清醒的认识到它可能带来的弊端和影响。请充分评估和测试后再为我所用。

欢迎关注我的公众号: