3

I have subscribed for Oracle CQN messages (with python cx_oracle) from one table on update. I am updating only one row:

UPDATE my_table SET 
  REMARKS = 'TEST2'
WHERE my_table_primary_key = 123456;

After commit I am receiving 2 messages for same table, same operation (UPDATE) but with 2 different ROWID's: '1234567890AAhF5AAF' and '1234567890AAhaqAAA'

Both ROWID's are pointing to the same table (my_table). After following select i have two lines with same id:

SELECT DBMS_ROWID.rowid_object('1234567890AAhF5AAF') AS ID FROM dual
UNION ALL
SELECT DBMS_ROWID.rowid_object('1234567890AAhaqAAA') AS ID FROM dual;

enter image description here

But what is strangest - only one ROWID is pointing to the real record, select by another one returning nothing (but NOT an invalid ROWID!):

select * from my_table where rowid = '1234567890AAhF5AAF'
UNION ALL
select * from my_table where rowid = '1234567890AAhaqAAA';

enter image description here

Can some one suggest what is happening?

Aleksandr Beliavski
  • 131
  • 1
  • 9
  • 2
    Do you have a LOB in this table? You can dig into the ROWID components using DBMS_ROWID - the ROWID indicates the block and row in a specific datafile where the row's data can be found, but for a LOB this is often a totally separate datafile from the rest of the row, so it'd make sense that there could be a secondary ROWID for the LOB. – kfinity Dec 16 '19 at 14:41
  • 1
    You can try to find the segment with something like `select * from dba_extents where relative_fno = DBMS_ROWID.rowid_relative_fno('1234567890AAhF5AAF') and DBMS_ROWID.rowid_block_number('1234567890AAhF5AAF') between block_id and block_id + blocks` – kfinity Dec 16 '19 at 15:04
  • 1
    REMARKS is this a partition key of the table? And you have enable row movement? – Arkadiusz Łukasiewicz Dec 16 '19 at 15:28
  • 1
    Posting the definition of the table would be helpful (index-organized tables, for example, are different than heap-organized tables, partitioned tables add in some additional considerations). – Justin Cave Dec 16 '19 at 15:30
  • 1
    Is "row movement" enabled for that table? –  Dec 16 '19 at 15:52
  • @kfinity Thanks for response! Table definitely has LOB, I will try to investigate data segments for than table. And thanks other guys also, I will search for "row movement" option and will go back tomorrow. – Aleksandr Beliavski Dec 16 '19 at 17:28

0 Answers0