In production every now and then we get an ORA-0001 error when dequeuing a message. We use:
1 Solaris 10
2 Oracle 10g
3 C++ application
4 PROC*C with dbms_aq package for queuing operations
5 Queues with XML payload
6 We handle a large volume of messages (1K/min).
Any clue why would a dequeue result in an ORA-0001 (unique constraint) error?
No it is not an issue I have ever come across on dequeue. The only time I could imagine this happening is on enqueue if the MSGID is not unique. Only on AIX have i seen an issue related to ORA-1 on enqueue.
On dequeue your msgid has already been created and you are effectively just selecting out the message.
The only way I could you progressing this would be to set an errorstack on ORA-1 but that would be more trouble than it is worth unless you can be reasonably sure you can reproduce the issue within a small timeframe otherwise you will get lots of tracefiles for perfectly legitimate ORA-1 errors.