一次Insert 引发ora-00942排查处理

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 

业务反馈insert单表引发 ora-00942 但count(*)表 是存在的且未连错业务用户，如下

用sqlplus、 plsql查看,表是存在的

 

 

起初怀疑是bug导致，对 表做insert 、move做10046分析

trace中关键步骤如下 

PARSE ERROR #811865616:len=138 dep=1 uid=0 oct=6 lid=0 tim=1126128285618 err=942
update "AMBASE"."MLOG$_DUG_D西药房门诊处方" set snaptime$$ = :1  where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
EXEC #763319368:c=140400,e=444314,p=0,cr=1879,cu=1051,mis=0,r=41935,dep=0,og=1,plh=664976654,tim=1126128285706
ERROR #763319368:err=604 tim=1126128285720
STAT #763319368 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD AS SELECT  (cr=945 pr=0 pw=783 time=357645 us)'
STAT #763319368 id=2 cnt=41935 pid=1 pos=1 obj=80045 op='TABLE ACCESS FULL DUG_D西药房门诊处方明细 (cr=887 pr=0 pw=0 time=16293 us cost=208 size=5444010 card=41877)'
STAT #811212528 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=2 pr=0 pw=0 time=89 us)'
STAT #811212528 id=2 cnt=2 pid=1 pos=1 obj=0 op='BUFFER SORT (cr=2 pr=0 pw=0 time=35 us)'
STAT #811212528 id=3 cnt=1 pid=2 pos=1 obj=627 op='TABLE ACCESS CLUSTER MLOG$ (cr=2 pr=0 pw=0 time=16 us cost=1 size=48 card=1)'
STAT #811212528 id=4 cnt=1 pid=3 pos=1 obj=626 op='INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)'
CLOSE #811212528:c=0,e=49,dep=1,type=0,tim=1126128285841
CLOSE #811865616:c=0,e=1,dep=1,type=0,tim=1126128285856
WAIT #763319368: nam='log file sync' ela= 29993 buffer#=122 sync scn=2491854764 p3=0 obj#=80046 tim=1126128316015
WAIT #763319368: nam='SQL*Net break/reset to client' ela= 7 driver id=1413697536 break?=1 p3=0 obj#=80046 tim=1126128316451
WAIT #763319368: nam='SQL*Net break/reset to client' ela= 150 driver id=1413697536 break?=0 p3=0 obj#=80046 tim=1126128316632
WAIT #763319368: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=80046 tim=1126128316664

MLOG$是相关物化视图日志表，并且尝试对表重命名 无法操作报ora-26563

问题已经清晰了，处理方式删除物化视图相关日志表

SQL> drop materialized view log on DUG_D西药房门诊处方明细;
 
Materialized view log dropped

最终问题解决