DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Advance Queueing table removal

  1. #1
    Join Date
    May 2002
    Posts
    22

    Cool

    Hi All,

    I've successfully startup my database (8.1.6) with one of
    the datafile was made offline due to corrupted. I don't have any backup.
    I'm unable to drop the tablespace since it contains some
    advance queueing Queue Tables.

    I use "DBMS_AQADM.DROP_QUEUE_TABLE ('table_name')"
    to drop the table, but it doesn't work due to some entries
    remain in the queue. It also cannot be deleted as its datafile
    is offline....

    Anyone can help, how to drop the tablespace...

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Can U explain to me . What is
    DBMS_AQADM.DROP_QUEUE_TABLE

    Is it a new package in oracle 8i ?





    Jack@TMSD2

  3. #3
    Join Date
    May 2002
    Posts
    22

    Smile

    Hi Li Gang / Jack

    Oracle8 offers a new facility called Oracle AQ (Oracle Advanced Queuing) that makes it much easier for developers to build applications that require deferred execution of activity. Oracle is positioning Oracle AQ as an alternative to the queuing mechanisms of teleprocessing monitors and messaging interfaces. Oracle AQ will serve as a foundation technology for workflow management applications, both those delivered by Oracle Corporation itself and those implemented by third parties.

    Before AQ users can enqueue and dequeue, they must have queues with which to work. The AQ administrator must create queue tables and queues within those tables and then start the queues. Additional administrative tasks include stopping queues and removing queue tables, managing lists of subscribers, and starting and stopping the Queue Monitor.

    The DBMS_AQADM package provides an interface to the administrative tasks of Oracle AQ. In order to use these procedures, a DBMS_AQADM user must have been granted the role AQ_ADMINISTRATOR_ROLE from the SYS account.

    If you have Advance queueing queue table and you want to drop it, you can only use DBMS_AQADM....


    (Still in S'pore???)

  4. #4
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Lye San (XDOCK Using AQ) Said :
    DBMS_AQADM.Stop_queue to stop the Queue and you should be able to drop it .



    I am still in Singapore .
    We need oracle nuts like you :-)
    Jack

  5. #5
    Join Date
    May 2002
    Posts
    22

    Question

    Hi.. Lye san


    I've try the following command, but it still doesn't work
    since the data file of the table is offline (corrupted).


    SQL> exec dbms_aqadm.stop_queue('MAJOR_QUEUE',true,true,false);

    PL/SQL procedure successfully completed.

    SQL> exec dbms_aqadm.drop_queue('MAJOR_QUEUE',true);
    BEGIN dbms_aqadm.drop_queue('MAJOR_QUEUE',true); END;

    *
    ERROR at line 1:
    ORA-00376: file 13 cannot be read at this time
    ORA-01110: data file 13: 'C:\MAIL3.DBF'
    ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2463
    ORA-06512: at "SYS.DBMS_AQADM", line 162
    ORA-06512: at line 1

    SQL> exec dbms_aqadm.drop_queue_table('MAJOR_QTABLE');
    BEGIN dbms_aqadm.drop_queue_table('MAJOR_QTABLE'); END;

    *
    ERROR at line 1:
    ORA-24012: cannot drop QUEUE_TABLE, some queues in TEST.MAJOR_QTABLE have not
    been dropped
    ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2670
    ORA-06512: at "SYS.DBMS_AQADM", line 192
    ORA-06512: at line 1


    ??

  6. #6
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448

    Exclamation

    Hello!
    The Queue-Table cannot b e dropped due to the corrupted Datafile.
    You have an inconsistent database now with no Hope because there is no hot/cold/logical backup.

    Probably DROP TABLESPACE INCLUDING CONTENTS would help?
    I am not sure.

    Orca

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