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_TAB LE1_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_TAB LE1_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

"_phil_" <bidon_fr (AT) hotmail (DOT) com> 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')

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._SC VAT_IN_QUEUE_TABLE_T',force=>TRUE)
BEGIN
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TEST._SC VAT_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:

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 (DOT) com> wrote

e=>TRUE)
e=>TRUE);

no more !

SQL> execute
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TESTAQ$_ ESS_CLI_FAC_QUEUE_TABLE1_H',force=>TRUE);
BEGIN
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TESTAQ$_ ESS_CLI_FAC_QUEUE_TABLE1_H',force=>TRUE);
END;

*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater
than 24 characters
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2699
ORA-06512: at "SYS.DBMS_AQADM", line 192
ORA-06512: at line 1

SQL>

same with
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TESTAQ$_ ESS_CLI_FAC_QUEUE_TABLE1_H');


g wrote:

_phil_ wrote:
shouldn't that table name include a period???
dbms_aqadm.DROP_QUEUE_TABLE(queue_table=>'TEST.AQ$ _ESS_CLI_FAC_QUEUE_TABLE1_H');
(period located after TEST and before AQ)

HTH
ed
Don't you just hate typos? When will we get a DWIM,NWIS interface?
(Do What I Mean, Not What I Say)

_phil_ schreef:

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Right right

Thats just because I am in a hurry

Thx to you
Phil

Frank van Bortel a écrit :