ORA-08103 错误解决

笔记本电脑 装的Windows版本 数据库 出现

selet count(*) from wrh$_active_session_history;

ERROR at line 1:
ORA-08103: object no longer exists

idle@0> select /*+ index(a WRH$_ACTIVE_SESSION_HISTORY_PK) */ count(*) from WRH$_EVENT_HISTOGRAM;
select /*+ index(a WRH$_ACTIVE_SESSION_HISTORY_PK) */ count(*) from WRH$_EVENT_HISTOGRAM
                                                                    *
ERROR at line 1:
ORA-08103: object no longer exists

通过错误得知，对象已经不存在，在数据文件里已经没有，被删除了，而数据字典信息没有被删除。

而我的数据库出现这个原因，就是因为是system.dbf 是很早以前的，我还原出来的，所以和sysaux.dbf 不是一致的

解决办法：删除数据字典信息

可以先删除一下分区表，用10046 看一下 oracle 递归调用了哪些sql

alter session set events='10046 trace name context forever,level 12';

alter table test1.mytest drop partition t2;

alter session set events='10046 trace name context off';

过滤如下dml操作

C:\Users\xujun>cat D:\oracle\diag\rdbms\myoracle\myoracle\trace\myoracle_ora_3124.trc|grep -E "delete|update"update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4delete from sys.cache_stats_1$ where dataobj# = :1delete from hist_head$ where obj# = :1select inserts, deletes  from   sys.mon_mods$  where  obj# = :objnselect inserts, deletes  from   sys.mon_mods_all$  where  obj# = :objnselect log, sysdate, youngest, youngest+1/86400,  oldest, oldest_pk, oldest_oid, oldest_new, oldest_seq,  oscn, oscn_pk, oscn_oid, oscn_new, oscn_seq, flag  from sys.mlog$  where master = :2 and mowner = :1 for updatedelete from superobj$ where subobj# = :1delete from tab_stats$ where obj#=:1update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1update partobj$ set partcnt = :1 where obj# = :2delete from tabpart$ where obj# = :1delete from obj$ where obj# = :1update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=: and block#=:3update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is nullupdate seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=: and block#=:3delete from seg$ where ts#=:1 and file#=:2 and block#=:3C:\Users\xujun>D:\oracle\diag\rdbms\myoracle\myoracle\trace\myoracle_ora_3124.trc

实际代码

SELECT  p_timestamp, p_obj#,d_obj# dependency$  where d_obj#=85249 and p_obj#=85249update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4delete from sys.cache_stats_1$ where dataobj# = :1delete from hist_head$ where obj# = :1select inserts, deletes  from   sys.mon_mods$  where  obj# = :objnselect inserts, deletes  from   sys.mon_mods_all$  where  obj# = :objnselect log, sysdate, youngest, youngest+1/86400,  oldest, oldest_pk, oldest_oid, oldest_new, oldest_seq,  oscn, oscn_pk, oscn_oid, oscn_new, oscn_seq, flag  from sys.mlog$  where master = :2 and mowner = :1 for updatedelete from superobj$ where subobj# = :1delete from tab_stats$ where obj#=:1update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1<span style="color:#ff0000;">update partobj$ set partcnt =2 where obj# =6301</span><span style="color:#ff0000;">delete from tabpart$ where obj# = 85164;delete from obj$ where obj# = :1</span>update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is nullupdate seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3<span style="color:#ff0000;">delete from seg$ where ts#=:1 and file#=:2 and block#=:3</span>

再alter system flush buffer_cache;

再select 就没有问题，茹有问题，需要重启一下数据库，把数据刷新到磁盘里。