Good day, respective all!
Environment: Oracle 12.2 64-bit under Windows.
Parameters:
Job_queue_processes = 4000
Aq_tm_processes = 1
After some experiments with enqueue and dequeue via callback I decided to drop the queues. It was successful. Unregister of PL/SQL callback package was also successful.
But I cannot drop a PL/SQL package itself.
Because there were some messages, stuck in AQ_SRVNTFN_TABLE_Q, and even after dropping queue and unregistering callback, PLSQL_NTFN job tries to deliver these messages to non-existent queue, and locks the package.
And more strange things occurs…
Say, my old queue was named OLD_QUEUE.
I created new queue(named NEW_QUEUE) and registered a new callback. I included logging inside this new callback. By the way, logging was also included into old callback.
When I dequeue messages to NEW_QUEUE, they are not dequeued.
Instead of this, Oracle calls OLD CALLBACK for OLD_QUEUE and finishes with exception “Queue OLD_QUEUE does not exists”.
Of course, it is not exists!
And the same time my new callback is never called. All messages remain in NEW_QUEUE in “Ready” state.
The strange thing is that OLD CALLBACK is called ONLY when I enqueue messages in NEW_QUEUE.
The problem is : I have no rights to stop scheduler jobs, I have no rights to kill sessions of user SYS.
Please, tell me:
May be just purge of AQ_SRVNTFN_TABLE_Q will be enough for stop countless Oracle attempts to do things with non-existent queue and non-existent registration?
If it is not enough: is there a way to clear some Oracle global views, where, I believe, old registration hangs?
If there is no such a views, can I be sure, that restart of EMON will fix the problem(without restarting instance)?
Any help would be very appreciated.
TIA,
Andrew.
question from:
https://stackoverflow.com/questions/65913232/oracle-scheduler-aq-job-hangs 
