I'm not going to make this longer than necessary, for the simple reason that i could not find a way to scientifically re-create the conditions that led to this error, although i managed to see it twice.
This happened on Oracle XE running on Xubuntu linux.
select * from USER_QUEUE_SUBSCRIBERS;Basically it means that you managed to corrupt the DBMS_AQ environment.
ORA-00942: table or view does not exist
ORA-06512: at "SYS.AQ$_GET_SUBSCRIBERS", line 54
Interestingly, in order to do so, you are not required to perform any fancy operations, it could be enough to drop some definition, sometime, somehow.
The first time i saw this message, i attempted to find out which table or view was missing, but after half an hour spent rummaging in the oracle dictionary i gave up and restored the db from a backup. As the DBMS_AQ environment is made up of several tables that are created and deleted on the fly when you run procedures like DBMS_AQADM.CREATE_QUEUE_TABLE and so forth, it's very likely that something went awry when i dropped a queue before dropping the subscribers or something like that.
A few days later i corrupted it a second time and i noticed that if i dropped and recreated all the queue related objects again, the error disappeared.
Interestingly enough, it seems that the other view ALL_QUEUE_SUBSCRIBERS continues to work without problems even when USER_QUEUE_SUBSCRIBERS raises an exception.
Don't ask me why.
See message translations for ORA-00942 and search additional resources.
No comments:
Post a Comment