Home » RDBMS Server » Server Administration » ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE (Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production)
ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682575] Thu, 29 October 2020 10:54 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,

I am trying to remvoe queue tables from a schema.

SQL>
SQL> set lines 900
SQL> col status for a20
SQL> col table_name for a35
SQL> col owner for a30
SQL>
SQL> SELECT owner,table_name,status FROM DBA_TABLES WHERE TABLE_NAME = 'AQ$_SOMELOGIC_QTABLE' AND OWNER = 'MYUSER';

OWNER                          TABLE_NAME                          STATUS
------------------------------ ----------------------------------- --------------------
MYUSER                          AQ$_SOMELOGIC_QTABLE               VALID

SQL>

So I am connecting and trying to drop it:


SQL> conn MYUSER/password@connstring
Connected.
SQL>
SQL>
SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1


SQL>

I executed The same with the administrative power user ( don't have SYS since I'm on AWS RDS )
and also tried to remove all queue related components but no luck:


SQL> conn poweruser/pupass@connstring
Connected.
SQL>
SQL>
SQL> --REMOVE QUEUE:
SQL> begin
  2    for i in ( SELECT name,queue_table FROM user_queues u) LOOP
  3       DBMS_AQADM.STOP_QUEUE(queue_name => i.name );
  4       DBMS_AQADM.DROP_QUEUE(queue_name => i.name );
  5       DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => i.queue_table, force => TRUE);
  6       END LOOP;
  7       END;
  8       /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --REMOVE QUEUE tables:
SQL> begin
  2    for i in ( SELECT queue_table FROM user_queue_tables u) LOOP
  3       DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => i.queue_table);
  4       END LOOP;
  5       END;
  6       /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --remove streams
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT owner,table_name,status FROM DBA_TABLES WHERE TABLE_NAME = 'AQ$_SOMELOGIC_QTABLE' AND OWNER = 'MYUSER';

OWNER                          TABLE_NAME                          STATUS
------------------------------ ----------------------------------- --------------------
MYUSER                         AQ$_SOMELOGIC_QTABLE               VALID

SQL>

Same for a similar attempt on the MYUSER again:



SQL> conn MYUSER/password@connstring
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> --REMOVE QUEUE:
SQL> begin
  2    for i in ( SELECT name,queue_table FROM user_queues u) LOOP
  3       DBMS_AQADM.STOP_QUEUE(queue_name => i.name );
  4       DBMS_AQADM.DROP_QUEUE(queue_name => i.name );
  5       DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => i.queue_table, force => TRUE);
  6       END LOOP;
  7       END;
  8       /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --REMOVE QUEUE tables:
SQL> begin
  2    for i in ( SELECT queue_table FROM user_queue_tables u) LOOP
  3       DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => i.queue_table);
  4       END LOOP;
  5       END;
  6       /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --remove streams
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT owner,table_name,status FROM DBA_TABLES WHERE TABLE_NAME = 'AQ$_SOMELOGIC_QTABLE' AND OWNER = 'MYUSER';

OWNER                          TABLE_NAME                          STATUS
------------------------------ ----------------------------------- --------------------
MYUSER                         AQ$_SOMELOGIC_QTABLE               VALID

SQL>
And if I try to force drop it same result:



SQL>
SQL> show user
USER is "MYUSER"
SQL>
SQL>
SQL>
SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1


SQL>
What am I doing wrong ?
Will be very thankful for any assistance..

Many thanks in advance,
Andrey R
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682578 is a reply to message #682575] Thu, 29 October 2020 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

AQ$_SOMELOGIC_QTABLE% are the default names for the tables associated to queue SOMELOGIC_QTABLE.
This explains the first and last errors.

Try to drop this queue if it exists or directly the table if it doesn't.
One can create a table starting with AQ$ without being a queue table:
SQL> create table aq$_foo (id int);

Table created.
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682677 is a reply to message #682578] Sun, 08 November 2020 06:04 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Thu, 29 October 2020 18:45

AQ$_SOMELOGIC_QTABLE% are the default names for the tables associated to queue SOMELOGIC_QTABLE.
This explains the first and last errors.

Try to drop this queue if it exists or directly the table if it doesn't.
One can create a table starting with AQ$ without being a queue table:
SQL> create table aq$_foo (id int);

Table created.

I think I might have made a little mess masking my code ( I accidentally masked the "_L", "_S"... suffixes that indicate that it is some sort of a related but not identical table to AQ$_SOMELOGIC_QTABLE ) .

Sorry for that, I did a more accurate job this time I hope.

I am querying the queues of MYUSER, no results:

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL> conn MYUSER/password@connstring
Connected.
SQL>
SQL>
SQL> select * from dba_queues du where du.OWNER = user;

no rows selected
I am checking which tables I still see with the AQ$ prefix. There are 6 of them:

SQL> set lines 900
SQL> select owner,table_name from dba_tables where owner = user and table_name like 'AQ$%';

OWNER                                                                                                                            TABLE_NAME
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_L
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_S
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_G
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_H
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_I
MYUSER                                                                                                                       AQ$_SOMELOGIC_QTABLE_T

6 rows selected.

SQL>


So I am trying to normally drop one of the tables AQ$_SOMELOGIC_QTABLE_L:

SQL> drop table AQ$_SOMELOGIC_QTABLE_L purge;
drop table AQ$_SOMELOGIC_QTABLE_L purge
           *
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table MYUSER.AQ$_SOMELOGIC_QTABLE_L



When I fail to do it, I try to do it with the DROP_QUEUE procedure:


SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE_L, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1

icon5.gif  Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682754 is a reply to message #682677] Sun, 15 November 2020 10:09 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Any ideas, anyone Sad ?
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682755 is a reply to message #682754] Sun, 15 November 2020 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

set lines 100 trimout on trimspool on pages 1000
show user
select owner,name,queue_table,queue_type from dba_queues order by 1, 2, 3;
select owner,queue_table from dba_queue_tables order by 1, 2;
select owner,queue_name,queue_table,consumer_name from dba_queue_subscribers order by 1,2,3;
Format the columns so the lines fit in 100 characters-width without wrapping.
Remove the lines that are irrelevant to the question (like all %SYS% queues).

Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682763 is a reply to message #682755] Tue, 17 November 2020 11:27 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Sun, 15 November 2020 20:23

set lines 100 trimout on trimspool on pages 1000
show user
select owner,name,queue_table,queue_type from dba_queues order by 1, 2, 3;
select owner,queue_table from dba_queue_tables order by 1, 2;
select owner,queue_name,queue_table,consumer_name from dba_queue_subscribers order by 1,2,3;
Format the columns so the lines fit in 100 characters-width without wrapping.
Remove the lines that are irrelevant to the question (like all %SYS% queues).


Here:

SQL> set lines 100 trimout on trimspool on pages 1000
SQL>
SQL> show user
USER is "MYUSER"
SQL>
SQL>
SQL> select owner,name,queue_table,queue_type
  2  from dba_queues
  3  where owner = 'MYUSER'
  4  order by 1, 2, 3;

no rows selected

SQL>
SQL>
SQL>
SQL> select owner,queue_table
  2  from dba_queue_tables
  3  where owner = 'MYUSER'
  4  order by 1, 2;

no rows selected

SQL>
SQL>
SQL> select owner,queue_name,queue_table,consumer_name
  2  from dba_queue_subscribers
  3  where owner = 'MYUSER'
  4  order by 1,2,3;

no rows selected

SQL>
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682764 is a reply to message #682763] Tue, 17 November 2020 11:58 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member

However, as in one of the posts above, they still appear as regular tables that still exist:


SQL> show user
USER is "MYUSER"
SQL>
SQL>
SQL> col owner for a30
SQL> col table_name for a50
SQL>
SQL> select owner,table_name from dba_tables where owner = user and table_name like 'AQ$%';

OWNER                          TABLE_NAME
------------------------------ --------------------------------------------------
MYUSER                     AQ$_SOMELOGIC_QTABLE_G
MYUSER                     AQ$_SOMELOGIC_QTABLE_H
MYUSER                     AQ$_SOMELOGIC_QTABLE_I
MYUSER                     AQ$_SOMELOGIC_QTABLE_L
MYUSER                     AQ$_SOMELOGIC_QTABLE_S
MYUSER                     AQ$_SOMELOGIC_QTABLE_T

6 rows selected.

I don't know what would would be the correct way to classify these and how to treat them,
Since Oracle gives confusing errors, treating them as AQ tables at first:



SQL> drop table AQ$_SOMELOGIC_QTABLE_L;
drop table AQ$_SOMELOGIC_QTABLE_L
           *
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table MYUSER.AQ$_SOMELOGIC_QTABLE_L

But when I try to use what I think is the appropriate procedure to drop a queue table the error indicates differently:

SQL>
SQL>
SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE_L, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1


SQL>
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682765 is a reply to message #682764] Tue, 17 November 2020 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try
exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE);
without the "_L".

Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682766 is a reply to message #682765] Tue, 17 November 2020 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also try:
Alter session set events '10851 trace name context forever, level 2;
before "drop table AQ$_SOMELOGIC_QTABLE_L purge;". This was a 9i/10g trick, not sure it still works in 19c (this event told Oracle to allow dropping a table named AQ$ with DROP TABLE even if it is an actual queue table).

[Updated on: Tue, 17 November 2020 15:20]

Report message to a moderator

Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682772 is a reply to message #682765] Wed, 18 November 2020 03:15 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Tue, 17 November 2020 23:08

Try
exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE);
without the "_L".

I tried but it gives the same message:


SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1

I believe the error is appearing due to the AQ$ prefix and not the actual nature of the object attempted dropping with the procedure:

SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_bla_bla', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_bla_bla', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_BLA_BLA, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1
Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682773 is a reply to message #682772] Wed, 18 November 2020 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what about my last post?

Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682774 is a reply to message #682766] Wed, 18 November 2020 03:22 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Tue, 17 November 2020 23:13

You can also try:
Alter session set events '10851 trace name context forever, level 2;
before "drop table AQ$_SOMELOGIC_QTABLE_L purge;". This was a 9i/10g trick, not sure it still works in 19c (this event told Oracle to allow dropping a table named AQ$ with DROP TABLE even if it is an actual queue table).


I tried it but no luck:

SQL> Alter session set events '10851 trace name context forever, level 2';

Session altered.

SQL> exec   DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'AQ$_SOMELOGIC_QTABLE_L', force => TRUE); END;

*
ERROR at line 1:
ORA-24079: invalid name AQ$_SOMELOGIC_QTABLE_L, names with AQ$_ prefix are not valid for QUEUE_TABLE
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 944
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7379
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1
Also tried the same with regular drop table:


SQL> Alter session set events '10851 trace name context forever, level 2';

Session altered.

SQL> drop table MYUSER.AQ$_SOMELOGIC_QTABLE_L;

Table dropped.

SQL>

It worked !!!

Great thanks Michel.

Re: ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682775 is a reply to message #682773] Wed, 18 November 2020 03:26 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 18 November 2020 11:17

And what about my last post?


Just answered it.

I appreciate the help greatly.

Been here for years and can't stop admiring this place and the people who run it.
Previous Topic: Did I set up UTL_MAIL correctly and completely?
Next Topic: Database Upgrade - Minimum Disruption
Goto Forum:
  


Current Time: Thu Mar 28 17:12:24 CDT 2024