Tuesday, July 05, 2005

Neat trick to get rid of ORA-24002

Here's a nifty way to get rid of ORA-24002 (I'm still trying to figure out how I ended up with this error -- all I did was import some tables and then tried to clean them up):
  1. Log into SQL*Plus or Server Manager as a user with DBA privileges.

  2. Issue this command:

    alter session set events '10851 trace name context forever, level 2';

  3. Now you can go ahead and drop the problem table.
The post in the link goes on to explain why this works:
Solution Explanation: =====================
Event 10851 disables error 24005 when attempting to manually drop a queue table. It should be noted that this is the "Hard Way" of dropping queue tables, and should only be practiced after all formal procedures, i.e., using the "DBMS_AQADM.DROP_QUEUE_TABLE" procedure, has failed to drop the table.