Oracle Database Discussions

I am studying and planning a toy project (which will run on 23ai Free or Always Free ADB) using TxEventQ

The queue was created with

begin
    dbms_aqadm.create_transactional_event_queue(
        queue_name         => NOTIFICATION_Q',
	    storage_clause     => 'TABLESPACE my_tablespace STORAGE (INITIAL 64k next 64k)',
        multiple_consumers => false,
	queue_payload_type => DBMS_AQADM.JMS_TYPE
    );
end;
/

This created 4 tables:

• AQ$_NOTIFICATION_Q_L
• AQ$_NOTIFICATION_Q_T
• AQ$_NOTIFICATION_Q_X
• NOTIFICATION_Q

Each of these have multiple partitions. All the partitions have 8M initial size except NOTIFICATION_Q (partitions are 64k due to the storage_clause). Together with LOBs, indexes etc, a queue is around 100MB when empty.

When I enqueue something, new partitions are created in NOTIFICATION_Q with 8M size.

Also, even retention was set to 0 (default), records were not deleted from NOTIFICATION_Q after dequeue. As the queue has multiple_consumers => false, I would expect records to disappear immediate after dequeue.

It seems TxEventQ is a storage monster and the storage usage is uncontrollable. Or have I missed something?

Will traditional AQ be better?