readonly的库部分表查询时报错

一个历史资料库，开启为read only状态，查询表R_FEEDER_ID_T时报错（不管下什么条件都报错，甚至select 1 from R_FEEDER_ID_T也不行）

ORA-00604: 递归SQL级别1出现错误

ORA-16000: 打开数据库以进行只读访问

这个表是一个普通表，不是global temp table,也没有什么特殊的地方，没有trigger.

做10046 trace发现有这么一些信息

=====================
PARSING IN CURSOR #11 len=44 dep=1 uid=1 ct=192 lid=1 tim=1276370678012537 hv=3719699332 ad='656b8508'
ALTER PUBLIC SYNONYM "R_FEEDER_ID_T" COMPILE
END OF STMT
PARSE #11:c=0,e=274,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1276370678012532
=====================
PARSING IN CURSOR #1 len=214 dep=2 uid=51 ct=47 lid=51 tim=1276370678012699 hv=2624602396 ad='656a8628'
BEGIN
    LBACSYS.lbac_events.before_alter(dbms_standard.dictionary_obj_type,
                             dbms_standard.dictionary_obj_name,
                             dbms_standard.dictionary_obj_owner);
  END;
END OF STMT
PARSE #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1276370678012695
EXEC #1:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,tim=1276370678012922
=====================

还有两条update：

=====================
PARSING IN CURSOR #8 len=78 dep=2 uid=0 ct=6 lid=0 tim=1276370678014051 hv=1963305302 ad='656a14b8'
update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4
END OF STMT
PARSE #8:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1276370678014046
EXEC #7:c=0,e=2811,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1276370678016944
FETCH #7:c=0,e=53,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,tim=1276370678017055
EXEC #11:c=0,e=4561,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=1,tim=1276370678017163
=====================
PARSING IN CURSOR #1 len=54 dep=1 uid=0 ct=6 lid=0 tim=1276370678017644 hv=3946387688 ad='656886f8'
update syn$ set node=:2,owner=:3,name=:4 where obj#=:1
END OF STMT
PARSE #1:c=0,e=398,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1276370678017639
EXEC #1:c=0,e=851,p=0,cr=2,cu=2,mis=1,r=0,dep=1,og=4,tim=1276370678018595
ERROR #1:err=16000 tim=1741938016
=====================

结合起来看ALTER PUBLIC SYNONYM "R_FEEDER_ID_T" COMPILE
,update dependency$ ,syn$应该是和同义词有关的东东

select * from dba_objects where object_name='R_FEEDER_ID_T'发现一个同义词status为invalid

同时其它同义词status为invalid的表查询也报同样的错

将数据库重启，开成read write模式，查询一下这几个同义词失效的表，再看同义词的状态已经变成valid了

再将数据库关掉，开启成read only模式，这时再查之前有问题的表，已经正常了

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

转载于:http://blog.itpub.net/10640532/viewspace-696992/