Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Tr : Re : AQ dequeue question

Tr : Re : AQ dequeue question

From: Bernard Polarski <>
Date: Fri, 26 May 2006 06:20:41 -0700 (PDT)
Message-ID: <>

<!-- DIV {margin:0px;}--> nah, none of them will do. I read in the doc : (Oracle® Streams Advanced Queuing User's Guide and Reference 10g Release 2 (10.2) Part Number B14257-01)        

  message grouping : This optional parameter specifies whether messages are grouped or not. The default NONE means each message is treated individually. TRANSACTIONAL means all messages enqueued in one transaction are considered part of the same group and can be dequeued as a group of related messages.    

  So it speaks only of the way the messages are enqueued, nothing about the content of data that are stored into the any_data.    

   Sort_list : it deals with enqueue in the propagation queues after the capture. I read in the doc :    

  The sort_list parameter determines the order in which messages are dequeued. You cannot change the message sort order after you have created the queue table. Your choices are:     ENQ_TIME
 COMMIT_TIME                       I don't think it is possible at all. By definition user_data hold anonymous content with only a schema address. It will be distributed by the reader process to the applier processe that will execute the command over the right object  

 Marius want to identifiy the content of these anonymous before they are distributed and skip all duplicated of a row to only extract the last. So he want the anonymous to have some kind of tag which their anonymous property forbid. Another problem is defining 'mutated' in streams.   

 Since user_data does not recognize rowid, then it relies only on full column comparison. So in fact he will never find his mutated rows    

  if we create a row with PK A and a colum value 'B' then mutate the colume value to 'C' we still update in the table the same row    

  <rowid> A B    

  the rows becomes    

   <rowid> A C    

   You stil know that it is the same row due to the row_id and that row has mutated. It is perfectly possible that A C is not the mutation of A B. For instance you can create 'A B' ,then delete 'A B' and creatre 'A C'. In this case you could not say that A C is the mutation of 'A B'   It is the row_id that tells you it is the mutation.        

  In streams you do not have the row id :    

  row 'A B' and 'A C' will be considedred different.     

  There is no way to tell that user_data 'A C' is in fact user_data 'A B' mutated or if row 'A C' is a new row that appeared after the deletion of 'A B'.      

 B. Polarski

What about using SORT_LIST and/or MESSAGE_GROUPING parameters of dbms_aqadm.create_queue_table? Otherwise, defining a user message type where you declare a column like PRIORITY/SEQUENCE and getting max from this column after dequeuing or using correlation_id with message enqueuing may be an area to explore.


Bernard Polarski <> a écrit : <!-- DIV {margin:0px;}-->Do you mean you want the apply process to skip rows in the queue table and jump directly to the last occurence of this row? You can alway write your own dml handler but rows are compared with the full value of every columns, that's why you activated the suplemental logs. I doubt you will earn something with your dml handler. The heaviest part in streams are the update and you want to detect them each.  

 As of attibute that could palys in ordered for a single column you could use the SCN of t he user_data with something like that :      


 lcr sys.LCR$._ROW_RECORD
 object_scb number;
 trn_id varchar2 ;  


    for p_x in (select user_data from AQ$_<QUEUE_RECEIVER>     loop

      rc:= p_x.user_data.getobject(lcr)   # load the user_data into LCR
     -- use scn  
      object_scn := lcr.get_scn(); 
      -- or use the trancaction id  is a varchar - which is the sequentiel id of the user_data        

   end loop;

 all this will take you far in time development  


 -----Original Message-----
 From: rjamya []
 Sent: Thursday, 25 May, 2006 9:32 PM
 To: Oracle Discussion List
 Subject: AQ dequeue question    

 We have a process that tracks changes to a table, whenever a column is  changes, pk is inserted in a AQ. There is a daemon process that does  some processing based on this value.  

 It may happen that in a short burst, one row may get modified multiple  times. Now we want to avoid re-processing the same row based on  entries in AQ.  

 Is there a way to get a ordered (rather distinct) set for dequeue by  specifying an attribute in the USERDATA part of the payload when  dequeuing?  

 Oracle is 9.2

  Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.

Received on Fri May 26 2006 - 08:20:41 CDT

Original text of this message