今天查看数据库的信息发现如下报警
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多多的！我们应该了解每一个新特性带给我们的便捷和优势，更重要的是我们要清醒的认识到它可能带来的弊端和影响。请充分评估和测试后再为我所用。
