Loading...

Monday, September 28, 2009

ORA-00942 when querying USER_QUEUE_SUBSCRIBERS

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

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;

ORA-00942: table or view does not exist
ORA-06512: at "SYS.AQ$_GET_SUBSCRIBERS", line 54
Basically it means that you managed to corrupt the DBMS_AQ environment.
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:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles