ORA-20000:DBMS_TABCOMP_TEMP_UNCMP
来源:互联网 发布:大数据 论文 人类 编辑:程序博客网 时间:2024/06/06 02:35
今天查看数据库的信息发现如下报警
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_DURATION2017-03-23 22:03:02 +000 00:00:092017-03-22 22:03:02 +000 00:08:202017-03-21 22:03:01 +000 00:00:122017-03-20 22:03:02 +000 00:08:462017-03-19 06:03:02 +000 00:00:042017-03-19 22:03:34 +000 00:00:042017-03-19 18:03:21 +000 00:00:032017-03-19 14:03:07 +000 00:00:042017-03-19 10:03:53 +000 00:00:062017-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 ENABLEDauto space advisor ENABLEDsql 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 DISABLEDauto space advisor ENABLEDsql tuning advisor ENABLED
此时“auto optimizer stats collection”任务已经被禁用,目的达到。
再一次印证:ORACLE新特性总是BUG多多的!我们应该了解每一个新特性带给我们的便捷和优势,更重要的是我们要清醒的认识到它可能带来的弊端和影响。请充分评估和测试后再为我所用。
欢迎关注我的公众号:
- ORA-20000:DBMS_TABCOMP_TEMP_UNCMP
- 偶遇Oracle 11g 的DBMS_TABCOMP_TEMP_UNCMP和DBMS_TABCOMP_TEMP_CMP
- ORA-20000:
- ORA-20000: ORU-10027
- ORACLE 常见错误及解决汇总 ORA-20000 ORA-03113 ORA-14452 ORA-08103
- ORA
- ora
- ORA-20000故障排除手记
- ORA-20000故障排除手记
- Oracle ORA-20000 "SYS.DBMS_OUTPUT"
- ORA-20000: -1652:ORA-01652: unable to extend temp segment by 8192 in tablespace
- ORA-01034 ORA-27101
- ORA-10567,ORA-00313
- ora-01128,ora-00275
- ORA-03113,ORA-07445
- ORA-32017 ORA-16179
- ORA-12012,ORA-12005
- ora-01034 ,ora-27101
- viewpager结合toolbar怎么实现点击viewpager,让toolbar消失,然后点击再让toolbar出现
- TCP/IP、Http、Socket的区别
- SQL is NULL 和 = NULL区别
- JSON深入浅出
- Spring声明式事务管理的多种方式
- ORA-20000:DBMS_TABCOMP_TEMP_UNCMP
- mysql试题
- Android Studio 更换SVN项目地址方法
- App Store 审核指南
- JNDI学习总结(二)——Tomcat下使用C3P0配置JNDI数据源
- Linux权限以及Stat命令中的Access、Modify、Change
- Effective Java读书笔记——第十一章 序列化
- freemarker 一些简单指令的使用
- C++实现双向链表