OraFAQ Forum: Server Administration » ORA-24079:names with AQ$_ prefix are not valid for QUEUE

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-24079:names with AQ$_ prefix are not valid for QUEUE_TABLE [message #682575]

Thu, 29 October 2020 10:54

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

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

Report message to a moderator

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

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Any ideas, anyone Sad

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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).

Report message to a moderator

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

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>

Report message to a moderator

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

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>

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Try

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

without the "_L".

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what about my last post?

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Did I set up UTL_MAIL correctly and completely?
Next Topic:	Database Upgrade - Minimum Disruption

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 18:12:53 CST 2025