Forum Stats

  • 3,840,354 Users
  • 2,262,592 Discussions
  • 7,901,237 Comments

Discussions

Not able to drop a schema due to a queue table

sudiptabhaskar1
sudiptabhaskar1 Member Posts: 6
edited Jul 27, 2016 3:55AM in Advanced Queueing

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.

b762b3ee-c208-4769-b72d-626c374088b5

Answers

  • sudiptabhaskar1
    sudiptabhaskar1 Member Posts: 6
    edited Jul 22, 2016 11:36AM

    DB : 12.1.0.2

    Server : RHEL 7 x86-64

  • gdanby
    gdanby Member Posts: 221 Bronze Badge
    edited Jul 27, 2016 3:55AM

    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.

    b762b3ee-c208-4769-b72d-626c374088b5
This discussion has been closed.