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.
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
SQL> execute dbms_aqadm.DROP_QUEUE_TABLE('TEST._SCVAT_IN_QUEUE_TABLE_T');
BEGIN dbms_aqadm.DROP_QUEUE_TABLE('TEST._SCVAT_IN_QUEUE_TABLE_T'); 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
Another idea ?
g wrote:
>
> i cannot remember the correct syntax
> but it is something like
> dbms_aqadm.drop_queue('my_queue');
> dbms_aqadm.drop_queue_table('my_queue_table')
>
>
>> 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, last try
>> 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
>>
>>
>> WHAT CAN I DO ???????????
>>
>> Thx
>> Phil
Received on Wed Sep 13 2006 - 10:18:24 CDT