Cannot drop user (ENQUEUING)

Hi,

I have a serious problem :(
I need to remove a scheme in a database which use enqueuing.

1. Here is my first try:

SQL> drop user TEST cascade;
drop user TEST cascade
*

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

2) Ok, so I looked at the DBA_QUEUES table to check if I have queues to disable.

SQL> select OWNER, NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED from DBA_QUEUES where OWNER='TEST';

no rows selected

SQL> hum, something is wrong ....

3) Let me have a look to the objects of that user

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where OWNER='TEST'

OWNER             OBJECT_NAME                          OBJECT_TYPE
-------------------------------------------------- ------------------
TEST        AQ$_ESS_CLI_FAC_QUEUE_TABLE1_H                     TABLE
TEST        AQ$_ESS_CLI_FAC_QUEUE_TABLE1_I                     TABLE
TEST        AQ$_ESS_CLI_FAC_QUEUE_TABLE1_T                     TABLE
TEST        AQ$_ESS_CLI_FAC_QUEUE_TABLE_I                      TABLE
TEST        AQ$_ESS_CLI_FAC_QUEUE_TABLE_T                      TABLE
TEST        AQ$_ESS_CLI_FAC_Q_TABLE_H                          TABLE
TEST        AQ$_ESS_CLI_FAC_Q_TABLE_I                          TABLE

4) OK, move on
SQL> execute dbms_aqadm.stop_queue('TEST.AQ$_SCVAT_IN_QUEUE_TABLE1_I', TRUE, TRUE, FALSE);
or
SQL> execute dbms_aqadm.stop_queue('TEST._SCVAT_IN_QUEUE_TABLE1_I', TRUE, TRUE, FALSE); and I obtain:

BEGIN dbms_aqadm.stop_queue('TEST.AQ$_SCVAT_IN_QUEUE_TABLE1_I', TRUE, TRUE, FALSE); END;
*

ERROR at line 1:

ORA-24010: QUEUE TESSTEST.AQ$_SCVAT_IN_QUEUE_TABLE1_I does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2822
ORA-06512: at "SYS.DBMS_AQADM", line 235
ORA-06512: at line 1

5) Last try
SQL> execute
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TEST._SCVAT_IN_QUEUE_TABLE_T',force=>TRUE) BEGIN
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TEST._SCVAT_IN_QUEUE_TABLE_T',force=>TRUE); END;           *
ERROR at line 1:

ORA-24002: QUEUE_TABLE TEST._SCVAT_IN_QUEUE_TABLE_T does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2699
ORA-06512: at "SYS.DBMS_AQADM", line 192
ORA-06512: at line 1

Unfortunatly, it does not work anymore because the queues does not exist but the table are here !!

In fact my tables have a bad prefix (AQ$_) and Oracle seems to think - but its wrong - that they are enqueued tables.

WHAT CAN I DO ??????????? Received on Wed Sep 13 2006 - 10:33:44 CDT