转载 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
2024-08-26 20:56:55
http://www.xifenfei.com/2011/12/some-indexes-or-index-subpartitions-of-table-vas-tab_pub_calllog-have-been-marked-unusable.html
1、检查alert日志发现错误
Wed Nov 30 13:36:47 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:49 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:50 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:51 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:53 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:54 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:55 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:58 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:37:12 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 22:00:09 2011 ………… Wed Nov 30 22:00:15 2011 GATHER_STATS_JOB encountered errors. Check the trace file . Wed Nov 30 22:00:15 2011 Errors in file /opt/oracle/admin/ora9i/bdump/ora9i_j001_21372 .trc: ORA-20000: index "VAS" . "XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state |
2、查看trace文件
/opt/oracle/admin/ora9i/bdump/ora9i_j001_21372 .trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/oracle/product/10 .2.0 /db_1 System name: Linux Node name: localhost.localdomain Release: 2.6.18-92.el5 Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008 Machine: x86_64 Instance name: ora9i Redo thread mounted by this instance: 1 Oracle process number: 42 Unix process pid: 21372, image: oracle@localhost.localdomain (J001) *** 2011-11-30 22:00:15.782 *** ACTION NAME:(GATHER_STATS_JOB) 2011-11-30 22:00:15.763 *** MODULE NAME:(DBMS_SCHEDULER) 2011-11-30 22:00:15.763 *** SERVICE NAME:(SYS$USERS) 2011-11-30 22:00:15.763 *** SESSION ID:(532.60095) 2011-11-30 22:00:15.763 ORA-20000: index "VAS" . "XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state *** 2011-11-30 22:00:15.782 GATHER_STATS_JOB: GATHER_TABLE_STATS( '"VAS"' , '"TAB_XN_CALLLOG_ANALYSIS"' , '""' , ...) ORA-20000: index "VAS" . "XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state |
3、日志初步结论
通过alert日志,感觉应该是对分区表操作,导致”VAS”.”XN_CALLLOG_ANALYSIS_PK”索引变成了unusable state,然后在数据库自动收集统计信息的时候报错(最大可能是全局index导致)
4、验证猜测是否正确
SQL> SELECT owner,index_name,table_name,status FROM DBA_indexes 2 WHERE index_name= 'XN_CALLLOG_ANALYSIS_PK' AND owner= 'VAS' ; OWNER INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- VAS XN_CALLLOG_ANALYSIS_PK TAB_XN_CALLLOG_ANALYSIS UNUSABLE |
5、解决相关问题问题
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || 'REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;' FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER NOT IN ( 'SYS' , 'SYSTEM' , 'PUBLIC' ) AND STATUS = 'UNUSABLE' UNION ALL SELECT 'alter index ' ||OWNER || '.' || A.INDEX_NAME || ' REBUILD online nologging;' FROM DBA_INDEXES A WHERE OWNER NOT IN ( 'SYS' , 'SYSTEM' , 'PUBLIC' ) AND STATUS = 'UNUSABLE' ; |
执行生成sql,解决相关index unusable问题
最新文章
- Linus:C++是一种糟糕的语言
- MAT使用--转
- XCode6 生成prefix.pch文件
- Java使用Jdbc操作MySql数据库(一)
- Catch That Cow(poj 3278)
- DOM笔记(六):怎么进行JQuery扩展?
- 转】Maven学习总结(二)——Maven项目构建过程练习
- controller,link,compile不同
- java.lang.IllegalStateException: Required view 'text1' with ID 2131492943 for field 'mText' was not found. If this view is optional add '@Nullable' annotation
- (转)一个form表单实现提交多个action
- sql语句相关操作
- php面向对象1
- dfs序和欧拉序
- TypeScript,初次见面,请多指教 ?
- 【Spring学习】Spring的源码解析之路
- c++ string替换指定字符串
- 从零开始学 Web 之 CSS3(一)CSS3概述,选择器
- css中选项卡的实现
- 将tiff文件转化为jpg文件并保存
- HTML5 data属性