ORA-01555: "_SYSSMU9$" too small

问题：
Thu Apr 18 22:00:05 2013
ORA-01555 caused by SQL statement below (SQL ID: 718f2y09dnvbr, Query Duration=0 sec, SCN: 0x000e.57bdf6c7):
Thu Apr 18 22:00:05 2013
 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*)  from "SYS"."WRI$_OPTSTAT_IND_HISTORY" t
Thu Apr 18 22:00:05 2013
GATHER_STATS_JOB encountered errors.  Check the trace file.
Thu Apr 18 22:00:05 2013
Errors in file /oracle/admin/wxxrdb/bdump/wxxrdb_j001_12649.trc:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

mos解决方法：
drop index "SYS"."I_WRI$_OPTSTAT_IND_OBJ#_ST";

CREATE UNIQUE INDEX "SYS"."I_WRI$_OPTSTAT_IND_OBJ#_ST" ON "SYS"."WRI$_OPTSTAT_IND_HISTORY" ("OBJ#", SYS_EXTRACT_UTC("SAVTIME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX";

-------------------------------------------------------------

Dear customer, I still think this is a index corruption issue even analyze table succeeded. The index has some block pointed to an UNDO block no longer exists, that's why it will get ora-1555 immediately. ACTION PLAN ----------------------- 1)Please check if you could read data from table successfully: select /*+ full(t) */ count(*) from SYS.WRI$_OPTSTAT_IND_HISTORY t; 2)If the step 1 succeeded, then please recreate this index: drop index "SYS"."I_WRI$_OPTSTAT_IND_OBJ#_ST"; CREATE UNIQUE INDEX "SYS"."I_WRI$_OPTSTAT_IND_OBJ#_ST" ON "SYS"."WRI$_OPTSTAT_IND_HISTORY" ("OBJ#", SYS_EXTRACT_UTC("SAVTIME")) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSAUX"; Then check again: select count(*) from SYS.WRI$_OPTSTAT_IND_HISTORY; Best Regards, Andy Zhang (张秋明) Global Software Support

 

Oracle 技术支持

- 7 天前

[ODM Test Case]

explain plan for select count(*) from SYS.WRI$_OPTSTAT_IND_HISTORY; select * from table (dbms_xplan.display(NULL,NULL,'ADVANCED')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2528882158 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| I_WRI$_OPTSTAT_IND_OBJ#_ST | 5640 | 11 (0)| 00:00:01 | --------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-759651/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27042095/viewspace-759651/