Oracle AQ: History Table for Queue Table

oracle

Is there a way to create a history table for a queue table, that will store all the events of the queues, for a defined period of time ?

Best Answer

It seems likely to me that you could use the retention property of the queue to do this. See http://docs.oracle.com/cd/B10500_01/appdev.920/a96587/qsample.htm#158007 and also dbms_aqadm.alter_queue for details. If you really need a separate table then how about creating a multiple subscriber queue and creating an audit subscription that populates the history table. I'd use built in functionality first.

Related Solutions

Can a Database request automatically remove itself from the queue after a delay

From Oracle 11gR1 you can use the WAIT instruction in DDL:

lock table t in exclusive mode wait 10;

This will wait 10 seconds before giving up.

The WAIT instruction has been available for a long time (9i) for DML:

SELECT * FROM t FOR UPDATE WAIT 10;

Disabling DBMS_SCHEDULER queue

I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.

Best Answer

Related Solutions

Can a Database request automatically remove itself from the queue after a delay

Disabling DBMS_SCHEDULER queue

Related Question