Advance Queueing table removal

[imron95]

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...

[ligang]

Can U explain to me . What is
DBMS_AQADM.DROP_QUEUE_TABLE

Is it a new package in oracle 8i ?





Jack@TMSD2

[imron95]

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???)

[ligang]

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

[imron95]

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


??

[Orca777]

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