Oracle AQ Notes

Submitted by code_admin on Mon, 07/23/2018 - 14:54

See also

Listing Objects

All queues - including exception queues

  1. select *
  2. from user_queues;
  3. SELECT *
  4.  FROM user_queue_tables;

Subscribers

  1. select *
  2. from "SYS"."DBA_QUEUE_SUBSCRIBERS"
  3. ;

Subscriber Registrations

  1. select *
  2. from "SYS".DBA_SUBSCR_REGISTRATIONS;

Queues and stats (Msgs waiting)

  1. select q.name, stats.*
  2. from "SYS"."V_$AQ" stats
  3. left join (
  4.   select *
  5.   from "SYS"."DBA_QUEUES"
  6. ) q on q.qid=stats.qid
  7. where ready>0

Deleting Objects

  1. begin
  2.   dbms_aqadm.stop_queue('AQ$_RJMTESTQ_001_QTAB_E');
  3.   dbms_aqadm.drop_queue('AQ$_RJMTESTQ_001_QTAB_E');
  4.   dbms_aqadm.drop_queue_table('RJMTESTQ_001_QTAB');
  5.   commit;
  6. end;

Altering Queue Props

  1. DBMS_AQADM.ALTER_QUEUE (
  2.    queue_name       =>'GMSELEMENTSYNCEBIZ',
  3.    max_retries      =>4,
  4.    retry_delay      =>60,
  5.    retention_time   =>5184000);
  6.    end;

Force drop queue

  1. begin
  2. DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => upper('SOA_JMS.JMS_QTAB'),FORCE=>true);
  3. end;

Oracle support note: https://blogs.oracle.com/db/entry/oracle_support_master_note_for_aq_que…

  1.  select *
  2.  from GV$QMON_COORDINATOR_STATS;
  3.  
  4.  select *
  5.  from GV$QMON_SERVER_STATS;

Queue Granting

  1. select *
  2. from QUEUE_PRIVILEGES
  3. ;

Start/Stop queue (for either enqueue or dequeue)

Param meening. If enqueue or dequeue is false keep current state. True to change start
Use START_QUEUE or STOP_QUEUE
The following code will do nothing unless false is changed to true

  1. BEGIN
  2. DBMS_AQADM.START_QUEUE(
  3. queue_name => 'test.obj_queue',
  4. enqueue => false
  5. dequeue => false
  6. );
  7. DBMS_AQADM.STOP_QUEUE(
  8. queue_name => 'test.obj_queue',
  9. enqueue => false
  10. dequeue => false
  11. );
  12. END;

Select properties from queue items

  1. select
  2.   q.queue,
  3.   TREAT (q.user_data as sys.aq$_jms_text_message).get_string_property('IC_REL_ORIG_QUEUE') as orig_queue,
  4.   TREAT (q.user_data as sys.aq$_jms_text_message).get_string_property('IC_REL_PHASE_ID') as phase_id,
  5.   TREAT (q.user_data as sys.aq$_jms_text_message).get_string_property('IC_REL_RESUBIMT_ID') as resubmit_id
  6. from soa_jms.aq$systemerror_qtab q
  7. order by
  8.   q.enq_timestamp desc
  9.  

Debug

Support master note:
https://blogs.oracle.com/db/entry/oracle_support_master_note_for_aq_que…

QMON - Queue Monitor Process

http://www.dba-oracle.com/t_v$_qmon_task_stats.htm

  1.  select *
  2. from v$qmon_coordinator_stats
  3. ;
  4.  select *
  5.  from GV$QMON_SERVER_STATS
  6.  ;
  7.  select *
  8. from v$qmon_tasks
  9. ;

Is there a task QMON_PERSISTENT_TM?
When did is start?

Healthcheck script oracle Doc ID 1193854.1

Drop subscriber

  1. DECLARE
  2.    subscriber       sys.aq$_agent;
  3. BEGIN
  4.    subscriber := sys.aq$_agent('subscriber1','aq2.msg_queue2', NULL);
  5.    DBMS_AQADM.REMOVE_SUBSCRIBER(
  6.       queue_name => 'aq.multi_queue',
  7.       subscriber => subscriber);
  8. END;

JMS Text Message

Output

  1. set serveroutput on
  2.  
  3. declare
  4.   l_message sys.aq$_jms_text_message;
  5.   l_messagePropArray sys.aq$_jms_userproparray;
  6.   l_messageProp sys.aq$_jms_userproperty;
  7.   l_message_data clob;
  8.   l_xml xmltype;
  9.   l_tmp varchar2(4096);
  10.   l_offset number := 1;
  11.   l_c number;
  12. begin
  13.   dbms_output.put_line('Start');
  14.  
  15.   select q.user_data
  16.   into l_message
  17. from
  18.   soa_jms.aq$jms_qtab q
  19. where msg_id='11BB7419A9850C6FE053AF39C69B8F3E'
  20.   ;
  21.  
  22.   l_message.get_text(l_message_data);
  23.   l_xml := XMLTYPE(l_message_data);
  24.  
  25.      loop
  26.          exit when l_offset > dbms_lob.getlength(l_message_data);
  27.          dbms_output.put_line( dbms_lob.substr( l_message_data, 255, l_offset ) );
  28.          l_offset := l_offset + 255;
  29.        end loop;
  30.  
  31.   l_messagePropArray := l_message.header.properties;
  32.  
  33.   dbms_output.put_line('');
  34.   dbms_output.put_line('Props');
  35.   dbms_output.put_line('=====');
  36.   for l_c in 1..l_messagePropArray.count LOOP
  37.     l_messageProp := l_messagePropArray(l_c);
  38.     dbms_output.put_line('Name=' || l_messageProp.name);
  39.     dbms_output.put_line('Type=' || l_messageProp.type);
  40.     dbms_output.put_line('str_value=' || l_messageProp.str_value);
  41.     dbms_output.put_line('num_value=' || l_messageProp.num_value);
  42.     dbms_output.put_line('java_type=' || l_messageProp.java_type);
  43.     dbms_output.put_line('-----');
  44.   END LOOP;
  45.  
  46. /*
  47.   select XMLSERIALIZE(Document l_xml as CLOB INDENT SIZE = 2)
  48.   into l_tmp
  49.   from dual;
  50.   dbms_output.put_line(l_tmp);  
  51.   */
  52.  
  53.  
  54.   dbms_output.put_line('End');
  55. end;

Remove Subscriber

Delete Subscriber

  1. DECLARE
  2.    subscriber       sys.aq$_agent;
  3.    queueSchema varchar2(1024) := 'soa_jms';
  4.    queueN varchar2(1024) := 'RJMTEST001';
  5.    subN varchar2(1024) := 'RJMTEST001';
  6. BEGIN
  7.    subscriber := sys.aq$_agent(subN,queueN, NULL);
  8.    DBMS_AQADM.REMOVE_SUBSCRIBER(
  9.       queue_name => queueSchema || '.' || queueN,
  10.       subscriber => subscriber);
  11. END;

Purging Queue Messages

  1. DECLARE
  2.   l_purge_opt dbms_aqadm.aq$_purge_options_t;
  3. BEGIN
  4.   l_purge_opt.block := TRUE;
  5.   dbms_aqadm.purge_queue_table(
  6.     queue_table => 'APPS.WF_BPEL_QTAB'
  7.     ,purge_condition => 'queue = ''WF_BPEL_Q'' AND MSG_ID=''1CCCCCC23'' AND qtview.msg_state=''PROCESSED'''
  8.     ,purge_options => l_purge_opt
  9.   );
  10. END;

See https://code2.metcarob.com/node/256

Release 12 AQ Changes

3 Teir background architecture
https://docs.oracle.com/database/121/ADQUE/componet.htm#ADQUE3663

  1. select *
  2. from GV$AQ_BACKGROUND_COORDINATOR
  3. ;
  4.  
  5. select *
  6. from GV$AQ_JOB_COORDINATOR
  7. ;
  8.  
  9. select *
  10. from GV$AQ_SERVER_POOL
  11. ;

Tags

RJM Article Type
Work Notes