CODEWITHSUNDEEP
oracle-database
satish
satish

Reputation: 11

SQL Error: ORA-24005: Inappropriate utilities used to perform DDL on AQ table VPAUTO.QT_REQS

During migration of Fastbrain database to VDC practice, we are trying to disable constraint before truncate tables as one of the steps.We are not able to disable the constraints on the queue tables. The traditional alter table disable constraint commands doesn’t work on queue tables.

Commands used:

ALTER TABLE VPAUTO.AQ$_QT_RESPS_F disable constraint SYS_C0019957; 

ALTER TABLE VPAUTO.AQ$_QT_REQS_F disable constraint SYS_C0019955; 

ALTER TABLE VPAUTO.AQ$QT_RESPS disable constraint SYS_C0019956; 

ALTER TABLE VPAUTO.AQ$QT_REQS disable constraint SYS_C0019954; 

ALTER TABLE VPAUTO.QT_RESPS disable constraint SYS_C0019903; 

ALTER TABLE VPAUTO.QT_REQS disable constraint SYS_C0019902;

Error:

Error starting at line 6 in command: ALTER TABLE VPAUTO.QT_REQS disable constraint SYS_C0019902

Error report:

SQL Error: ORA-24005: Inappropriate utilities used to perform DDL on AQ table VPAUTO.QT_REQS 24005. 00000 - "must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables"

*Cause: An attempt was made to use the SQL command DROP TABLE for queue tables, but DROP TABLE is not supported for queue tables.

*Action: Use the DBMS_AQADM.DROP_QUEUE_TABLE procedure instead of the DROP TABLE command.

Upvotes: 1

Views: 12601

Answers (1)

Krishna
Krishna

Reputation: 481

Alter statements cannot be used on AQ objects, only dbms_aqadm package procedures can be used for any DDL action on AQ tables, use Force option to drop forcefully if the error is repeating

Upvotes: 2

Related Questions