Not able to drop a schema due to a queue table

sudiptabhaskar1

Hi ,

  I am trying to drop a schema but it is throwing the queue table related errors.

SQL> DROP USER HDBIS CASCADE;

DROP USER HDBIS CASCADE

*

ERROR at line 1:

ORA-24008: queue table HDBIS.DEF$_AQCALL must be dropped first

Now I tried to follow oracle's recommendation.

SQL> alter session set events '10851 trace name context forever, level 1';

Session altered.

SQL> drop table HDBIS.DEF$_AQCALL;

Table dropped.

NOTE : I have dropped other %AQ% tables also.

SQL> alter session set events '10851 trace name context off';

Session altered.

SQL> DROP USER HDBIS CASCADE;

DROP USER HDBIS CASCADE

*

ERROR at line 1:

ORA-24008: queue table HDBIS.DEF$_AQCALL must be dropped first.

Still the same problem.

Tried some other suggestions from community also. But no result.

Connect to sys:

SQL> BEGIN

  2  DBMS_AQADM.DROP_QUEUE_TABLE(

  3  queue_table        => 'HDBIS.DEF$_AQCALL',

  4  force              => TRUE);

  5  END;

  6  /

BEGIN

*

ERROR at line 1:

ORA-24002: QUEUE_TABLE HDBIS.DEF$_AQCALL does not exist

ORA-06512: at "SYS.DBMS_AQADM", line 388

ORA-06512: at line 2

SQL> execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT('HDBIS.DEF$_AQCALL',TRUE);

BEGIN DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT('HDBIS.DEF$_AQCALL',TRUE); END;

*

ERROR at line 1:

ORA-24150: evaluation context DEF$_AQCALL. does not exist

ORA-06512: at "SYS.DBMS_RULE_ADM", line 229

ORA-06512: at line 1

After all this:

SQL> drop USER HDBIS cascade;

drop USER HDBIS cascade

*

ERROR at line 1:

ORA-24008: queue table HDBIS.DEF$_AQCALL must be dropped first

Please guide.

sudiptabhaskar1

DB : 12.1.0.2

Server : RHEL 7 x86-64

gdanby

There's a document on MOS about invalid DEF$_AQCALL queue tables after an Oracle 12 upgrade which suggests ways to remove invalid objects. It looks as though you've tried the first two suggested though:

sqlplus system/passwd
exec dbms_aqadm.drop_queue_table('DEF$_AQCALL', true);
exec dbms_aqadm.drop_queue_table('DEF$_AQERROR', true);

See Doc Id 961104.1.