I've been re-running a couple of scripts written for 19c to create a single-subscriber queue; there have been some changes in 23c because it introduced an IOT that didn't appear in 19c: AQ$_DEMO_AQ_TABLE_H, with its index segment SYS_IOT_TOP_100236.
Two anomalies appeared when I executed:
begin
dbms_aqadm.move_queue_table('demo_aq_table','test_8k_assm',DBMS_AQADM.MOVEQT_ONLINE);
end;
/
- The tablespace name had to be in upper case, though the table name was acceptable in lower case.
- After a successful move the IOT (aq$_demo_aq_table_h) had acquired a data_object_id of zero (rather than staying null).
I also had a problem on my first test because the procedure “failed” reporting:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 21276
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 21265
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 21404
Since I had enabled SQL trace I could see that this appeared to be happening (both times) for a cursor number that reported the text:
DROP TABLE "TEST_USER"."QT_INT100155" PURGE
The QT_INT100155 table was the renamed version of the original queue table and the rebuilt queue table (with all the trappings) seemed to be in place and working perfectly. There should have been no enqueue/dequeue activity going on when the problem happened, and no reason for the original queue table to be locked. After poking around for a few minutes I issued the same drop command from SQL*Plus and it worked okay. I have not been able to reproduce the problem.
Regards
Jonathan Lewis