问题：
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博客

ORA-01555: "_SYSSMU9$" too small

最新文章