How to drop a user having queue tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to drop a user having queue tables

  1. #1
    Join Date
    Jun 2003
    Posts
    1

    How to drop a user having queue tables

    How do I drop a user having queue tables?

    On removing the queue tables using

    execute dbms_aqadm.drop_queue_table('AQ$__FT_Q_BEIN_I',true);

    gives the following error
    ERROR at line 1:
    ORA-24002: QUEUE_TABLE DEMO61.AQ$__FT_Q_BEIN_I does not exist
    ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2955
    ORA-06512: at "SYS.DBMS_AQADM", line 197
    ORA-06512: at line 1

    There is no entry for this queue table in user_queue_tables but there is an entry for it in user_tables. Can someone help??

  2. #2
    Join Date
    May 2001
    Posts
    736

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    AQ$__FT_Q_BEIN_I is an exception queue, you cannot drop exception queues, only normal queues.
    In order to get the list of queues execute:

    SELECT OWNER, NAME, QUEUE_TABLE
    FROM DBA_QUEUES
    WHERE OWNER = '&owner'
    AND QUEUE_TYPE = 'NORMAL_QUEUE'
    /

    EXECUTE
    DBMS_AQADM.STOP_QUEUE(QUEUE_NAME=>'the queue name');
    DBMS_AQADM.DROP_QUEUE(QUEUE_NAME=>'the queue name');
    DBMS_AQADM.DROP_QUEUE_TABLE(QUEUE_TABLE=>'the queue name');

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width