Re: Cannot drop user (ENQUEUING)

_phil_ wrote:
> 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 ???????????
Open a service request/TAR with Oracle. There is a way to get these tables dropped and I have successfully used it with specific direction from Oracle Support. I cannot say how this is done, as I don't work for Oracle Support (and I won't assume the liability involved for giving out such information should something go wrong), which is why you need to open a TAR/SR.

David Fitzjarrell Received on Wed Sep 13 2006 - 11:16:02 CDT