Re: CANNOT DROP USER (ENQUEUING)

"_phil_" <bidon_fr_at_hotmail.com> wrote in message news:45081563$0$17175$626a54ce_at_news.free.fr...
> 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
>

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') Received on Wed Sep 13 2006 - 09:48:09 CDT