what happens if you leave the aq$ in
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TESTAQ$_ESS_CLI_FAC_QUEUE_TABLE1_H
')
"_phil_" <bidon_fr_at_hotmail.com> wrote in message
news:45082141$0$1714$626a54ce_at_news.free.fr...
> 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',forc
e=>TRUE)
> BEGIN
>
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TEST._SCVAT_IN_QUEUE_TABLE_T',forc
e=>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:52:01 CDT
