RIB - queues properties

PT : RIB - JMS AQ 

EN : RIB - JMS AQ

select qid,owner,name,queue_table,queue_type,max_retries,retry_delay,retention, enqueue_enabled,dequeue_enabled 
from dba_queues where (name like '%ETRECEIVING%' or name like '%ASNOUTAT%')
and queue_type = 'NORMAL_QUEUE' ; 

    318421 RIBAQ ETRECEIVING ETRECEIVINGTABLE NORMAL_QUEUE 5 0 0   YES     YES  
    311104 RIBAQ ETASNOUTAT ETASNOUTATTABLE   NORMAL_QUEUE 5 0 0   YES     YES  

-- max_retries 200
EXECUTE dbms_aqadm.alter_queue ( queue_name => 'ETRECEIVING', max_retries => 200 ); 
EXECUTE dbms_aqadm.alter_queue ( queue_name => 'ETASNOUTAT', max_retries => 200 ); 

--retention 1 day 
EXECUTE dbms_aqadm.alter_queue ( queue_name => 'ETRECEIVING', retention_time     => 86400 ); 
EXECUTE dbms_aqadm.alter_queue ( queue_name => 'ETASNOUTAT', retention_time     => 86400 ); 

select qid,owner,name,queue_table,queue_type,max_retries,retry_delay,retention, enqueue_enabled,dequeue_enabled 
from dba_queues where (name like '%ETRECEIVING%' or name like '%ASNOUTAT%')
and queue_type = 'NORMAL_QUEUE' ; 

  318421 RIBAQ ETRECEIVING ETRECEIVINGTABLE NORMAL_QUEUE 200 0 86400   YES     YES  
  311104 RIBAQ ETASNOUTAT ETASNOUTATTABLE   NORMAL_QUEUE 200 0 86400   YES     YES   



--- bulk situations - all queues in the schema 



select  * from all_queues where queue_type = 'NORMAL_QUEUE' and owner like 'RIBAQ' and retention <> '604800'


declare

stm_text varchar(200);

cursor c1 is (  select  name from all_queues where queue_type = 'NORMAL_QUEUE' and owner like 'RIBAQ' );
   
begin
 for i in c1  
  loop
   stm_text := 'begin dbms_aqadm.alter_queue ( queue_name => '''|| i.name  ||''', retention_time => 604800); end;';
   dbms_output.put_line(stm_text); 
   EXECUTE IMMEDIATE stm_text;
   
  end loop;
end;
/